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
03-21-2005, 09:41 AM
Have you tried the Select method instead of CurrentRegion?
03-21-2005, 10:12 AM
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.
03-21-2005, 12:04 PM
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?
03-21-2005, 03:50 PM
Yes, I get an error. Error 440: Object Error. That is the extent of help that I get from the error.
03-22-2005, 08:11 AM
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
03-22-2005, 06:53 PM
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)?