Faster way to copy and paste multiple columns in Sheet1 to corresponding columns in Sheet2

[babbeleme1]

I currently have a macro, which I recorded, that copies a column from sheet ("FinCen") and pastes it into a specific column in sheet ("ALL"). There are about 10 columns that I want to copy and paste, which takes a while because it does each one after the other. Is there a way to put them in an array and loop through to make the process run faster? A bit of the code is below.

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[oeldere]

If the code of John H.Davis works, this also will works, I add it to give you an (new) idea.

Please Login or Register  to view this content.

[babbeleme1]

These both make perfect sense. BUT, when I run this code, it gives me an error message "Runtime error 7. Out of memory"
Any ideas?

Also, I noticed that the codes you suggested will only copy and paste, but not paste link.

Thanks in advance!

[JBeaucaire]

Instead of dealing with whole columns, let's just use the same number of rows as used in column K of FInCen.

Please Login or Register  to view this content.

[babbeleme1]

Thanks JBeaucaire!

I'm not too familiar with the range format, but I'm assuming that Range("A2:A" & LR) takes the entire A column starting from A2 all the way until the last row, which has data?

One error I've run into so far... Where LR is set, it gives me an error saying "Application-defined or object-defined error"

Please Login or Register  to view this content.

[JBeaucaire]

Can we look at this workbook with the edited code in it together? Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[JBeaucaire]

Thanks for the feedback. Did you finally get it working?


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks.