find last row copy & paste

spq24

Board Regular
Joined
Jan 18, 2010
Messages
52
I need to combine 11 worksheets worth of data into one "master" worksheet and the dataset will grow throughout the year. I need code that will find out if cell g2 (on the sheets where the data is to be copied from) is populated and if it is it will copy the entire dataset (the last column is column X so it'd be for example copy row A2:X553). It will then copy this data and put it into the master sheet in the first unpopulated row. (example: copies the data from sheet2 (A2:X553) and pastes it into master (A2:X553), copies data from sheet3 (A2:X54) and pastes it into master after data from sheet 1 (A554:X608)). If anyone could please help me with this that would be a huge help! thank you
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
Test this in a copy of your workbook.

It assumes there is already a sheet called 'Master' and that any existing data (from row 2 down) on this sheet can be deleted.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Combine()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Master")<br>        .UsedRange.Offset(1).ClearContents<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Worksheets<br>            <SPAN style="color:#00007F">If</SPAN> ws.Name <> "Master" <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> Len(ws.Range("G2").Value) > 0 <SPAN style="color:#00007F">Then</SPAN><br>                    ws.UsedRange.Offset(1).Copy Destination:= _<br>                        .Range("A" & .Rows.Count).End(xlUp).Offset(1)<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> ws<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 

spq24

Board Regular
Joined
Jan 18, 2010
Messages
52
Unfortunately that did not work. For some reason it started at sheet 13 out of 15 and didn't copy the right things. It looks like it combined data from sheet 1 and the rest of the sheets some how
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
Unfortunately that did not work. For some reason it started at sheet 13 out of 15 and didn't copy the right things. It looks like it combined data from sheet 1 and the rest of the sheets some how
I'm going to be away for a few days but

1. Can you confirm that for the first 12 sheets, there is some data in cell G2?

2. What range was copied for sheets 13-15? What range should have been copied?

3. If it only started at sheet 13, how do you say it combined data from sheet 1 and the rest of the sheets?
 

spq24

Board Regular
Joined
Jan 18, 2010
Messages
52
What I did was copy and paste sheet2 and 5-13 into a new workbook and ran your macro. It still didn't work and I'm not sure why. I even took out the sheets where G2 was not populated, and I tried changing the format for G2 to a number (it's a transaction number that starts with 0 for all of them...will that matter?)

It didn't copy the right range either. It only copied sheets 13 (it copied the correct range), sheet 12 (copied 79 of 113 rows of data), and sheet 1 (it copied 325 of 553 rows of data). It didn't copy anyother sheets of data in.

n/m about the combining data it didn't do that
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
1. Did you also put a sheet named 'Master' into the new workbook?

2. Can you describe exactly where you put the macro code and how you 'ran' the macro?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,630
Messages
5,523,991
Members
409,554
Latest member
denistrevisan

This Week's Hot Topics

Top