Macro-Copy&Paste Fixed Data Ranges Into Last Empty Cell of Specific Column

My understanding of macros is limited to just recording it so any assistance would be great. I’m on a 14 hour deadline.

I have rather large Excel file that spans around 245 columns & has multiple users responsible for certain columns. Columns are chunked to provide data about a specific group. Every 72 hours, I need to provide to managers what’s currently on this file in a specific summary format.

I’ve recorded a macro that gets me half way there. Here’s where I’m clueless. After my macro deletes unnecessary columns, what remains are the columns for “Main Data” group + each of the 5 “Business” groups (Investors, Lawyers, Credit, Finance, and Support) which would have identical column headings.

(1) Name (2) Address (3) ID# (4) Control#

Furthermore, for each of the 5 “Business” groups, there are 9 slots (ie- a user can enter up to 9 unique entries for a particular group.)

Example- The Investor group contains 9 slots. Each slot will contain the 4 columns mentioned above. So there are 46 slots in total – 1 for Main Data, 45 (5 groups x 9 slots) for Business groups.

MAIN DATA (columns A-D)
Investor1 (columns E-H)… Investor2 (columns I-L)… Investor9 (columns AK-AN)
Lawyer1… Lawyer9
Compliance1… Compliance9
Finance1… Finance9
Support1… Support9

MY PROBLEM: After all this formatting mentioned above is done by the macro I recorded (which is the easy part), I need a macro to now cut & paste all data from each of the 45 Business groups slots & then paste at the bottom of the MAIN DATA slot.

The final file would be 4 columns (as listed above) wide. Data from each of the 45 business group slots would be one below the other- all of it underneath the data in the Main Data group.

I attempted to record a macro where I’m copying a section’s data range starting from row2 to row4000 (I know there will never be 4000 entries. I did this just to ensure that all future data would be captured) then pasting that after the last entry in the Main Data.

Example- I’d copy data from Investor1 slot(starting at column E2-H2) & go about 4000 rows down. Then I’d press CTRL+Down Arrow key in column A & then click the down arrow one time to take me into 1st empty cell where I would paste my copied data. I was going to repeat this process for the remaining 44 slots.

I’ve added some extra entries to test the macro & the problem is that the recorded macro is pasting data in specific cell location in columns A instead for looking for the 1st empty cell in column A & then pasting the copied data.

If anyone could provide me a macro to accomplish this, that would be great. If you could just provide a macro that copies & pastes data for the first 2 slots, I can repeat the language for the remaining 43 slots--


Copy data spanning from columns E2-H2 to E4002-H4002 (Investor1).
Paste copied data into the first blank cell in column A (MAIN DATA)
Copy data spanning from columns I2-L2 to I4002-L4002 (Investor2).
Paste copied data into the first blank cell in column A (MAIN DATA)… below the values I just pasted.


UPDATE: I've gone thought the net & tried to paste in a few VBA posts/tips that I thought would work. Here's what I have so far from tinkering around. I need someone to tell me what to replace the xxxxxxx line with. This would be the language to tell Excel to paste copied data range into the first blank cell in column A.

Please Login or Register  to view this content.

Thanks for the quick response. But there is an issue. The range that I am pasting into already has data in it. Your code copies & pastes the entire column which won't work in my case. It also seems to be capturing/pasting other column headings (row#1) to the right of the defined data range.

Since I have 46 data range "groups", data in columns A-D will grow as I paste in data. I need to be able to paste the specific data range that I've copied at the bottom of column A--- E2:H4202 I2:L4202 M2:P4202… FY2:GB4202 (last data range).

Once I repeat this process for the remaining data range groups, I will then record a macro to delete all columns except for A-D. The end product would be a very long list of data that was essentially stacked one on top of another. I hope this clarifies my issue. Thanks in advance.

This is cross posted, and has been answered:
http://www.mrexcel.com/forum/showthread.php?t=386439