-
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
-
Have you tried the Select method instead of CurrentRegion?
Paul
~~~~
Microsoft MVP (Visual Basic)
-
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
-
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)
-
Yes, I get an error. Error 440: Object Error. That is the extent of help that I get from the error.
-
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)
-
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
-
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
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|