Excel Copy & Paste
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("sheet2").range("A" & count4).currentregion
count4 = count4+1
count3 = count3+1
elseif usit1 <> ("USIT") then
count3 = count3+1
Have you tried the Select method instead of CurrentRegion?
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.
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?
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.
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
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)?