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

H

hailnorm

New Member
Joined
Apr 26, 2009
Messages
7
My understanding of macros is limited to just recording it so any assistance would be great.

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: 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 column A instead for looking for the 1st empty cell in column A & then pasting the copied data.

So far 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. 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.

Sub transposedata()
Sheets("ConsolidatedYTDReport").Select
Range("E2:H4202").Select
Selection.Copy
XXXXXXXXXXXX
ActiveSheet.Paste
End Sub


 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
hailnorm,

Try:

TEST this FIRST in a COPY of your workbook.
1) hit Alt + F11
2) go to [Insert] - [Module] then paste the code onto the right pane
3) hit Alt + F11 again

Code: 
Option Explicit
Sub transposedata()
Dim LR As Long
With Sheets("ConsolidatedYTDReport")
  LR = .Range("A1").End(xlDown).Row
  .Range("E2:H4202").Copy .Range("A" & LR + 1)
End With
End Sub
 
Last edited:
Upvote 0
Works perfect for my 1st defined data range.

I have to repeat this process for 45 more data ranges... copy a specific range & paste into the 1st blank cell in column A.

I tried to unsuccessfully add another data range. What am I doing wrong? The 2 ways I tried to modify you code to add new data range (in red). What is the language I need to repeat:

Attempt#1

Option Explicit
Sub transposedata()
Dim LR As Long
With Sheets("****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 10">****** name="Originator" content="Microsoft Word 10"><link rel="File-List" href="file:///C:%5CUsers%5Calex%5CAppData%5CLocal%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:ApplyBreakingRules/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:SimSun; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:宋体; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 680460288 22 0 262145 0;} @font-face {font-family:"\@SimSun"; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 680460288 22 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:SimSun;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]-->ConsolidatedYTDReport")
LR = .Range("A1").End(xlDown).Row
.Range("E2:H4202").Copy .Range("A" & LR + 1)
LR = .Range("A1").End(xlDown).Row
.Range("I2:L4202").Copy .Range("A" & LR + 1)
End Sub

Attempt#2

Option Explicit
Sub transposedata()
Dim LR As Long
With Sheets("ConsolidatedYTDReport")
LR = .Range("A1").End(xlDown).Row
.Range("E2:H4202").Copy .Range("A" & LR + 1)
Dim LR As Long
With Sheets("ConsolidatedYTDReport")
LR = .Range("A1").End(xlDown).Row
.Range("I2:L4202").Copy .Range("A" & LR + 1)
End Sub
 
Upvote 0
hailnorm,

Try:

Code: 
Option Explicit
Sub transposedata()
Dim LR As Long
With Sheets("ConsolidatedYTDReport")

  LR = .Range("A1").End(xlDown).Row
  .Range("E2:H4202").Copy .Range("A" & LR + 1)
  LR = .Range("A1").End(xlDown).Row
  .Range("I2:L4202").Copy .Range("A" & LR + 1)


End With
End Sub
 
Last edited:
Upvote 0
Thank you VERY MUCH!!!! :)

You just afforded me my about 4 hours of sleep tonight & a great start to a very busy work week.
 
Upvote 0
We've just added another column to each of the 46 data ranges so that now the data range is 5 columns wide instead of 4. I assumed simply modifying the range would do the trick but what's happening in that the copied data replaces exsiting data in Columns A-E instead of pasting it into the 1st empty cell in column A.

The 45 data ranges to the right of Column A-E are properly "stacking" one on top of another. And instead of this entire data stacking underneath existing data in column A-E, it replaces it.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
ORIGINAL--<o:p></o:p>
<o:p> </o:p>
LR = .Range("A1").End(xlDown).Row<o:p></o:p>
.Range("E2:H4202").Copy .Range("A" & LR + 1)<o:p></o:p>
LR = .Range("A1").End(xlDown).Row<o:p></o:p>
.Range("I2:L4202").Copy .Range("A" & LR + 1)<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
MODIFIED--<o:p></o:p>
<o:p> </o:p>
.Range("F2:J4002").Copy .Range("A" & LR + 1)<o:p></o:p>
LR = .Range("A1").End(xlDown).Row<o:p></o:p>
.Range("<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:place w:st="on">K2</st1:place>:O4002").Copy .Range("A" & LR + 1)<o:p></o:p>
LR = .Range("A1").End(xlDown).Row
 
Upvote 0
hailnorm,

Insert a column at column E.

Now your copied data will not overwrite any data.

You would have to shift all your five column wide ranges to the right one column.

4 columns wide "E2:H4202" now becomes five columns wide "F2:J4002", for all your 46 data ranges.

Or, you could stack all your five wide columns to another worksheet.
 
Upvote 0
Sorry if I wasn't clear. The columns in my file now look like this The column headings are identical for each data range as before except for a new column that's been added to each data range. FYI-- the new/5th column for MAIN DATA (column E) will likely not have data (not sure if this could cause an issue). I instered this column so that each data range is uniform.

MAIN DATA (columns A-E)
Investor1 (columns F-J)… Investor2 (columns K-O)… Investor9 (columns AT-AX)
Lawyer1… Lawyer9
Compliance1… Compliance9
Finance1… Finance9
Support1… Support9

I'm not having any success with your suggestion which I'm probably misinterpreting. I've inserted a column at E (hightlight E & then insert). The end result is things are still shifted.

I'm not sure why your original suggestion which worked perfectly for 4 columns not doesn't work for 5 if the only thing changed is the data range. The insertion point for the copied range now seems to be at A1 instead of the first empty cell in Column-A
 
Upvote 0
hailnorm,

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


And, post the macro code you are using.

At the beginning of your posted code, enter the following without the quote marks:
["code"]

Your code goes here.

At the end of your posted code, enter the following without the quote marks:
["/code"]


If not:

See my Private Message to you (top right hand corner of MrExcel, Welcome, hailnorm., "Your Notifications:".
 
Upvote 0
I have a recorded a macro that basically trims my master data tab columns & pastes it into a tab called “ConsolidatedYTDReport”. Data ranges (46 total) are chunked every 5 columns. Due to its size, I’ve only included a small section but should illustrate my problem.

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
This screenshot what the ConsolidatedYTDReport tab would look like. I put some dummy entries to test. I need the macro mentioned above to be modified to copy & paste all the data --chunked (F-J, K-O, P-T) ranging from row 2 to 4002—below existing data in the red colored heading section (refer to as Main Data section)
<o:p></o:p>

Excel Workbook
ABCDEFGHIJKLMNOPQRST
1Main NameControl NumberAddressID NumberDate of SubmissionInvestor-1Control NumberAddressID NumberDate of SubmissionCompliance-1Control NumberAddressID NumberDate of SubmissionFinance-1Control NumberAddressID NumberDate of Submission
2Borrower-1GCI-1Address 1Tax-1Investor1-1Investor1-1Investor1-1Investor1-1Investor1-1Compliance1-1Compliance1-1Compliance1-1Compliance1-1Compliance1-1Finance1-1Finance1-1Finance1-1Finance1-1Finance1-1
3Borrower-2GCI-2Address 2Tax-2Investor1-2Investor1-2Investor1-2Investor1-2Investor1-2Compliance1-2Compliance1-2Compliance1-2Compliance1-2Compliance1-2Finance1-2Finance1-2Finance1-2Finance1-2Finance1-2
4Borrower-3GCI-3Address 3Tax-3Investor1-3Investor1-3Investor1-3Investor1-3Investor1-3Compliance1-3Compliance1-3Compliance1-3Compliance1-3Compliance1-3Finance1-3Finance1-3Finance1-3Finance1-3Finance1-3
ConsolidatedYTDReport

<o:p></o:p>
<o:p></o:p>
<o:p></o:p>


Once the data from data range to data range is stacked one below another, I will then record another macro which will delete remaining column. The macro I’m discussing should be yielding me the following look:
<o:p></o:p>
<o:p></o:p>


Excel Workbook
ABCDEFG
1Main NameControl NumberAddressID NumberDate of Submission
2Borrower-1GCI-1Address 1Tax-1
3Borrower-2GCI-2Address 2Tax-2
4Borrower-3GCI-3Address 3Tax-3
5Investor1-1Investor1-1Investor1-1Investor1-1Investor1-1
6Investor1-2Investor1-2Investor1-2Investor1-2Investor1-2
7Investor1-3Investor1-3Investor1-3Investor1-3Investor1-3
8Compliance1-1Compliance1-1Compliance1-1Compliance1-1Compliance1-1
9Compliance1-2Compliance1-2Compliance1-2Compliance1-2Compliance1-2
10Compliance1-3Compliance1-3Compliance1-3Compliance1-3Compliance1-3
11Finance1-1Finance1-1Finance1-1Finance1-1Finance1-1
12Finance1-2Finance1-2Finance1-2Finance1-2Finance1-2
13Finance1-3Finance1-3Finance1-3Finance1-3Finance1-3
14
15
16
17
Final result

<o:p></o:p>
<o:p></o:p>
<o:p></o:p><o:p></o:p>

Here’s the macro code which seems to be replacing the 1<SUP>st</SUP> data range (A-E). Screenshot is below code—
<o:p></o:p>
<o:p></o:p>
Rich (BB code): 
Option Explicit
Sub transposedata()
Dim LR As Long
With Sheets("ConsolidatedYTDReport")
 
 .Range("F2:J4002").Copy .Range("A" & LR + 1)
   LR = .Range("A1").End(xlDown).Row
 .Range("<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on">K2</st1:place>:O4002").Copy .Range("A" & LR + 1)
   LR = .Range("A1").End(xlDown).Row
 .Range("P2:T4002").Copy .Range("A" & LR + 1)
   LR = .Range("A1").End(xlDown).Row
 
End With
End Sub
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Excel Workbook
ABCDEFGHIJKLMNOPQRST
1Investor1-1Investor1-1Investor1-1Investor1-1Investor1-1Investor-1Control NumberAddressID NumberDate of SubmissionCompliance-1Control NumberAddressID NumberDate of SubmissionFinance-1Control NumberAddressID NumberDate of Submission
2Investor1-2Investor1-2Investor1-2Investor1-2Investor1-2Investor1-1Investor1-1Investor1-1Investor1-1Investor1-1Compliance1-1Compliance1-1Compliance1-1Compliance1-1Compliance1-1Finance1-1Finance1-1Finance1-1Finance1-1Finance1-1
3Investor1-3Investor1-3Investor1-3Investor1-3Investor1-3Investor1-2Investor1-2Investor1-2Investor1-2Investor1-2Compliance1-2Compliance1-2Compliance1-2Compliance1-2Compliance1-2Finance1-2Finance1-2Finance1-2Finance1-2Finance1-2
4Compliance1-1Compliance1-1Compliance1-1Compliance1-1Compliance1-1Investor1-3Investor1-3Investor1-3Investor1-3Investor1-3Compliance1-3Compliance1-3Compliance1-3Compliance1-3Compliance1-3Finance1-3Finance1-3Finance1-3Finance1-3Finance1-3
5Compliance1-2Compliance1-2Compliance1-2Compliance1-2Compliance1-2
6Compliance1-3Compliance1-3Compliance1-3Compliance1-3Compliance1-3
7Finance1-1Finance1-1Finance1-1Finance1-1Finance1-1
8Finance1-2Finance1-2Finance1-2Finance1-2Finance1-2
9Finance1-3Finance1-3Finance1-3Finance1-3Finance1-3
ConsolidatedYTDReport
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Macro to copy and paste special transpose that repeats after each blank row
Replies
3
Views
156
jolivanes
jolivanes
P
  • Solved
Have copied data paste below occupied cell in Column A (offset)
Replies
5
Views
218
smozgur
smozgur
S
  • Solved
Shortcut to show/hide certain columns?
2
Replies
15
Views
342
Micron
M
C
  • Solved
Recording a Macro that Copies & Pastes into Other Sheets
Replies
1
Views
336
AlanY
A
P
  • Question
How to have hyperlinks pasted when data is copied
Replies
0
Views
132
Pophil
P

Forum statistics

Threads
1,216,616
Messages
6,131,757
Members
449,670
Latest member
ryanrodgers2014

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top