changing between worksheets

[JennyC64]

Hi, I have a spreadsheet which I have just set up. One function is to copy a row to a second obsolete worksheet and then delete from the main worksheet. This was working and has now stopped. I have put the code below, hope formatting works.

Please Login or Register  to view this content.

[pike]

try something like

Please Login or Register  to view this content.

[JennyC64]

I am testing now, just having problems with other code so cant get to test. So basically you got rid of all the worksheet.select or .activate and used the with statement instead. I am assuming then that the correct worksheet will then always keep the focus. Do you still need some sort of Application.CutCopyMode = False to clear the clipboard?

[JennyC64]

The numrows now returns 1 which is incorrect and then the copy fails with an automation error. So not having any luck with this and now heading home for the weekend, yay!!

[pike]

this should do it

Please Login or Register  to view this content.

[JennyC64]

Thanks, using this method of not activating the second work book worked well. I couldnt get the .copy to work but manually copied the few pieces of information I needed and got to work that way