Help! How do I do the following in one process instead of bouncing between 2 worksheets?

[andrewst123]

I'm too new to VBA to know how to approach this. My code works, but I think it's sloppy and I'm looking for a hand to save or copy each cell that I need in my original workbook and pasting it in specific different cells in my 2nd workbook. My way has the active workbook bouncing back and forth but there has to be a way to get it all in a single transfer which should improve speed and resources. I didn't include the rest of my code after this where I have an "Else" with other stuff, but this is where my problem lies. Any advice or help would be appreciated and you can assume I don't know much if you want to be really clear for me with you help I'd be really grateful!

Please Login or Register  to view this content.

[xlnitwit]

Remove all the lines that end in .Activate. All the other code is fully qualified so those lines are unnecessary.

[AlphaFrog]

You've actually done all the heavy lifting already. You've fully qualified each copy-paste line with the workbook and worksheet. So there is no need to Activate each workbook. Simply delete each line that has .Activate e.g.;...

Please Login or Register  to view this content.

As far as copy-pasting all the areas with an all-in-one command, there is not a way to do that. Each of your areas is different in size and in one case you are transposing the data. The way you have it now (sans the .Activate lines) isn't so bad.

A slightly Faster method than copy and PasteSpecial is simply the Destination.Value = Source.Value method e.g.;

Please Login or Register  to view this content.

The Destination and Source ranges have to be the same size. It only transfers Values, no formats or anything else.


One other tip; this really doesn't improve performance, but it makes your code more concise. If you set a worksheet variable for both your source an destination sheets, the parent workbooks are qualified in those worksheet variables and you can use a shorter syntax like this...

Please Login or Register  to view this content.