Getting error 1004 Select method of range class failed when i automatically run my macro

[cubangt]

At this moment i dont have a sample file that i can provide, if i can it would be next week and right now just looking for a quick solution until this is replaced.

So something i noticed and not sure what is the best solution at this moment.
When i run my macro, when it gets to this routine, i get this error:

error 1004 Select method of range class failed
On the 3rd line in the code below:
"ThisWorkbook.Worksheets("Cleanup").Range("A2").Select"

NOW, since it throws this error, if i simply select or make the "Cleanup" tab/sheet active, it will continue and complete successfully.

Im working on redoing this routine, but that wont be for a few more days maybe next week. And i have the rest of this week i have to run this report.
I havent had any issues with this until today, but today i swapped out another copy routine to use the advanced filter method to help speed things up.
Ill be trying to implement the same in this routine, but like i said not yet..

The only thing i can think is to add a "ThisWorkbook.Worksheets("Cleanup").Activate" before that line, but not sure thats the best solution. And then what will happen down towards the bottom when i need to paste the values into the other sheet "Mainrecords"

Please Login or Register  to view this content.

[TMS]

Probably just means the Cleanup sheet isn't selected when you run the routine whereas it was before you made the changes. I"ll try and look at it tomorrow if you haven’t had a better offer.

[cubangt]

For now ill just use the Activate until we replace routine, didnt seem to slow it down.
We are trying to make the overall macro run quicker when we have days worth of files to open and copy into the MainRecords tab.

So the below will also be replaced with the advanced filter method. Even with the routine below, the entire macro ran in under a second

"This code ran successfully in 0.8 seconds"

I ran it against a weeks worth of reports and the below didnt affects the overall performance much.

8 files worth and ran in under 6 seconds
"This code ran successfully in 5.87 seconds"

Please Login or Register  to view this content.

So the above temp solution will do until we can replace it with the other method. The whole reason for making these improvements was due to the fact that its typically done daily and when it is, only takes about 30 seconds, but on tuesday, had to process around 7 files and took little over 3 minutes.. so 5 secs is def ok for now.. and hopefully can be little faster when we replace this last routine.

[TMS]

Ok, will look tomorrow when I’m in front of a computer.

[cubangt]

Ok, will look tomorrow when I’m in front of a computer.

ok, thank you

[TMS]

Try:

Please Login or Register  to view this content.

[cubangt]

I appreciate the response and have it saved, but actually got it working earlier today with the following change over to an array
And that allowed the overall macro to complete in less than 1 second
"This code ran successfully in 0.77 seconds"

Please Login or Register  to view this content.

[TMS]

You're welcome. Thanks for the rep.

[TMS]

Not convinced your new code is quicker than mine. I tested your original code, your new code and my code against the same test data and my code consistently runs in half the time of the others. The Cleanup sheet has 26 columns by 2000 rows of data.

Please Login or Register  to view this content.

Please Login or Register  to view this content.