find last row copy & paste

[spq24]

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

 

[Peter_SSs]

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]

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]

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]

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]

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?