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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,585
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
50,585
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
50,585
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?
 

Forum statistics

Threads
1,141,224
Messages
5,705,122
Members
421,378
Latest member
CarlosDuran

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
Top