Excel Copy & Paste

[Portree]

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

[pclement]

Have you tried the Select method instead of CurrentRegion?

[Portree]

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

[pclement]

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?

[Portree]

Yes, I get an error. Error 440: Object Error. That is the extent of help that I get from the error.

[pclement]

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.

Sheets("Sheet1").Rows(3).Select
Sheets("Sheet1").Rows(3).Copy
Sheets("Sheet2").Select
Range("A9").Select
ActiveSheet.Paste

[Portree]

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

[pclement]

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)?