Slow Copy And Paste

Hi All,

Im having a little trouble with refining some vba in regards to a copy and paste performing slowly.

Here is what I am using:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Which runs slowly, probably becasue its copying the enitre columns.

Here is what I am attempting to use:

Please Login or Register  to view this content.

But I get a run time error 1004 on the line

Please Login or Register  to view this content.

What am I doing wrong?

Thanks!

This is my guess.
I do not know why are using like if you have a single worksheet, so I assumed that you want to loop through sheets and pick a sheet.
I am also not sure what is sheet x.

Untested code.

Please Login or Register  to view this content.

FWIW:

Please Login or Register  to view this content.

Originally Posted by AB33

This is my guess.
I do not know why are using like if you have a single worksheet, so I assumed that you want to loop through sheets and pick a sheet.
I am also not sure what is sheet x.

Untested code.

Originally Posted by JOHN H. DAVIS

FWIW:

Thanks Guys, sorry about the confusion, looking back at my original post I should have included the entire code and its purpose, not just the area I was looking to alter. Here is the entire code:

Please Login or Register  to view this content.

The idea here is to copy and paste data from an already open workbook without knowing the full name of the book or sheet the data is on. Both will contain the word "Changes" in its name, hence the wild cards.
With these new variables defined, do you both still recommend the code you have provided?

Thanks!

I would not do looping through open books. It would be faster to go through open files. What happens if you want loop through 100 open books, do they have to be some where on my PC windows?

Originally Posted by AB33

I would not do looping through open books. It would be faster to go through open files. What happens if you want loop through 100 open books, do they have to be some where on my PC windows?

The user will have at most 3-5 excel workbooks open at any given time, they are temporary files with exported data from a database. How would you improve looping through open files instead of workbooks?
Thanks!

No, I meant you use workbook.open method. Why can not save these tpm files in tmp folder and then open each wb from that tmp? I believe this work faster than looping though active wb.

You can also use ADO method and works on closed workbook. Please refer to Ron Deburin's site for ADO.

Originally Posted by AB33

No, I meant you use workbook.open method. Why can not save these tpm files in tmp folder and then open each wb from that tmp? I believe this work faster than looping though active wb.

You can also use ADO method and works on closed workbook. Please refer to Ron Deburin's site for ADO.

Thanks, I will definitely check Rons site out, ive visited it a few times, but not for ADO.

Originally Posted by Izandol

If you will copy entire columns, you may not past anywhere except row 1 of target sheet because the copied area will not fit! You may try:

Thanks Izandol, your method works well! It does grab a little too much data though, I;m just trying to grab the data in columns A:H (more data exists outside this range), and not the header (in row 1).
When I tried changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

I still got the header. Also, I wasnt able to manipulate just the A:H range. Please help!

If you a header in the region and want to exclude it.

Please Login or Register  to view this content.

Originally Posted by AB33

If you a header in the region and want to exclude it.

Please Login or Register  to view this content.

Thanks, that makes sense! Is there a way to only copy the data in columns A:H as well?

One of the 2.

Please Login or Register  to view this content.

Originally Posted by AB33

One of the 2.

Thank you very much, I used this one:

Please Login or Register  to view this content.

The first one only copied the first two rows of data for some reason.
Thanks again, marking this as solved!

How about this?

Please Login or Register  to view this content.

Originally Posted by AB33

How about this?

Please Login or Register  to view this content.

I'll give this one a try too, thanks!