Excel Copy & Paste


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Excel Copy & Paste

Hybrid View

  1. #1
    Join Date
    Aug 2004
    Posts
    16

    Excel Copy & Paste

    Hello All,

    I think this one should be easy, I just can't seem to get the right formula down. I have information that is laid out in rows on an excel spredsheet. When I get to a row that has a specific piece of information then I want to copy that whole row, and put it into a new sheet on the same Excel workbook. Below is the code that I am trying so far. Can someone let me know what is wrong. Thank you.

    if usit1 = ("USIT") then
    xls.sheets("usit_usdt").rows(count3).select
    xls.sheets("usit_usdt").row(count3).copy
    xls.sheets("sheet2").activesheet
    xls.sheets("sheet2").range("A" & count4).currentregion
    activesheet.paste
    count4 = count4+1
    count3 = count3+1
    goto end_proce1
    elseif usit1 <> ("USIT") then
    count3 = count3+1
    end if

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    Have you tried the Select method instead of CurrentRegion?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Aug 2004
    Posts
    16
    Thanks for the response PClement, but my code doesn't even get down that far. It stops at the second line when selecting the row to copy. So, I appreciate the feedback and will try that when I can get the code to run down to that level.

    Thanks

    Portree

  4. #4
    Join Date
    Dec 2003
    Posts
    2,750
    Could you be more specific? When you say it "stops" what does that mean? Are you getting an error? Is so, what is the error?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  5. #5
    Join Date
    Aug 2004
    Posts
    16
    Yes, I get an error. Error 440: Object Error. That is the extent of help that I get from the error.

  6. #6
    Join Date
    Dec 2003
    Posts
    2,750
    Here is some sample code below that will perform a copy and paste. BTW, the Macro recorder is a great tool for generating code. It's far from perfect but at least will provide a good start and help you learn the Excel object model.

    Code:
    Sheets("Sheet1").Rows(3).Select
    Sheets("Sheet1").Rows(3).Copy
    Sheets("Sheet2").Select
    Range("A9").Select
    ActiveSheet.Paste
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  7. #7
    Join Date
    Aug 2004
    Posts
    16
    Hi PClement,

    I have tried the record script and it didn't help either, I appreciate the idea though. I have tried what you wrote and I get a different error, it is "R440 "Microsoft Excel: Select method of Range class failed".

    So, still looking for more ideas. Thanks

    John

  8. #8
    Join Date
    Dec 2003
    Posts
    2,750
    Did you fully qualify the Range (using the Sheet object) or did you just copy my code as is without modification?

    Also, I can't tell whether you're running this code from Excel or from Visual Basic (or another client)?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center