Selecting Multiple Data Ranges

I built the following macro to help me pull 22,000+ rows of data from a spreadsheet that I get every Monday:

For ease of reading, Ill break it down into steps:

Step 1 - Renames the existing sheet to "852", adds a second sheet called "Dump", & creates headers for columns A:K in row 1.

Please Login or Register  to view this content.

Step 2 - Take data from column B row 13 to the last row of data in B based on column A and pasts it to "Dump" starting in C2:

Please Login or Register  to view this content.

Step 3, 4, & 5 - Similar to Step 2 except they are applied to and pasted in:

column D into "Dump" A2
column G into "Dump" H2
column H into "Dump" K2

Again you will see that I am selecting data from column (D,G or H) row 13 to the last row of data in B based on column A:

Please Login or Register  to view this content.

I feel like there is a more efficient way to code this so that steps 2 through 5 copy the respective data and then pastes in into the new sheet.

If it makes things easier I can have the data pasted in columns A through D on the Dump sheet.

Thanks!

Or maybe something like:

Please Login or Register  to view this content.

MarvinP,

Many thanks for condensing this down for me.

For some reason your code is not working. I keep getting a "Compile Error: Invalid or unqualified reference" and it highlights on ".Rows" in the line of code:

Please Login or Register  to view this content.

The code that Roy & Shred provided are working but I thought you would like to know.

Thanks again!!

RoyUK & ShredDude,

Thank you very much for the codes you provided. I ran both and came out with the same (excellent) result. Pats on the back for all of you (You too MarvinP)...

It never ceases to amaze me how many different ways you can code to get the same result.

Two questions....

You both use (in one form or another) the following code to copy and paste:

Roy's Code:

Please Login or Register  to view this content.

Shred's Code:

Please Login or Register  to view this content.

I see what the line of code does but I don't understand how the paste function execute.

Second question...

Can the data be pasted simply as value and not with the format?

Thanks.