Excel VBA copy sheet data from multiple sheets based on list

Question

Ok, here is my update based on code from below. What I need is to have any data from any sheet listed in 'Master' to fill in sheet 'Combined'. I will only have three columns of data in each sheet. Data from each sheet should start in Column A of 'Combined'.

   Private Sub CommandButton1_Click()
        Dim lLastRow As Long
        Dim i As Integer

        lLastRow = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
        last_col = 0

        For i = 2 To lLastRow
            MySheet = Worksheets("Master").Cells(i, 1).Value
            Worksheets(MySheet).Columns(1).Copy Worksheets("Combined").Columns(last_col + 1)
            last_col = Worksheets("Combined").Cells(1, Columns.Count).End(xlToLeft).Column
        Next
    End Sub

As you've not provided any code I'm assuming you've not made an attempt at the solution yet. This doesn't really need VBA (Would be over engineering it). Investigate the use of an indirect() formula. — Zerk, Apr 27 '17 at 16:51
Agree with @Zerk. Anyway, try to google for following pieces of code: 1) Loop through all cells with data in Master Column A. 2) For each cell in the loop, 3) set cell value in a variable (`TheCell=Worksheets("MASTER"),Cells(i,1)`), and 4) copy from desired sheet (`Worksheets("Combined").Range(DefineYourRangeHere)=Worksheets(TheCell).Range("A:B")` — CMArg, Apr 27 '17 at 16:53
The more I look on google the more confused I get. I can only find code samples to copy ALL data or from a specific sheet — Noob2Java, Apr 27 '17 at 17:39
[find last row](http://stackoverflow.com/a/71310/1726522); [loop through cells in range](http://stackoverflow.com/a/3875582/1726522); [asign cell value to variable](http://stackoverflow.com/a/9910727/1726522) (`MyVal`). Asign range (same link, but check `MyRNG`). — CMArg, Apr 27 '17 at 19:11

CMArg · Accepted Answer · 2017-04-28T19:24:24.173

0

Try the following.

Edited, according to OP comments (data "stacked")

Private Sub CommandButton1_Click()
    Dim lLastRow As Long
    Dim i As Integer

    lLastRow = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row 'Last row of Master Sheet
    lLastRowCombined = Worksheets("Combined").Cells(Rows.Count, 1).End(xlUp).Row 'Last row of Combined Sheet

    For i = 2 To lLastRow 'scan all rows in Master Sheet
        MySheet = Worksheets("Master").Cells(i, 1).Value 'MySheet stores the sheet name from list in Master, row i, column A
        lLastRow2 = Worksheets(MySheet).Cells(Rows.Count, 1).End(xlUp).Row 'For that Sheet, get the last row
        Worksheets(MySheet).Range("A1:C" & lLastRow2).Copy Worksheets("Combined").Range("A" & lLastRowCombined) 'copy the range into Combined Sheet
        lLastRowCombined = Worksheets("Combined").Cells(Rows.Count, 1).End(xlUp).Row + 1 'Get the NEW Last row of Combined Sheet
    Next
End Sub

edited Apr 28 '17 at 19:24

answered Apr 27 '17 at 19:56

CMArg

1,525
3
13
28

thanks @CMArg So you're saying this is only showing the last one because it continues to over-write? – Noob2Java Apr 27 '17 at 20:16
No. I edited the answer. Now code get last column, and add the data afterwards. Just give it a try. – CMArg Apr 28 '17 at 12:41
I ran the code and the 'Combined' sheet just has the last cell from column A from 'Master' – Noob2Java Apr 28 '17 at 13:15
I ran the code, and in the first column of "Combined" sheet is placed the data of ColumA from the sheet whose name is in Master A2, in the second column of "Combined" is placed the data of ColumnA from the sheet whose name is in Master A3, and so on. – CMArg Apr 28 '17 at 16:01
Ok, that seems to work, however, I need the combined data to be in column A. The way it is now, it places across columns A,B,C etc – Noob2Java Apr 28 '17 at 17:01
I think you should edit your question, since there is no indication of how you want the data placed in the "combined" sheet, and we are still bad at telepathy. – CMArg Apr 28 '17 at 17:09
I have updated above @CMArg Sorry for the confusion. – Noob2Java Apr 28 '17 at 17:28
Still not clear at all. Say, range `A1:C20` from `Worksheets("Apple")` into `Worksheets("Combined").Range("A1:C20")`, and then range `A1:C20` from `Worksheets("Orange")` into Worksheets("Combined").Range("A21:C40")`? Please edit your question again. – CMArg Apr 28 '17 at 18:43
Yes, that is exactly what I want to happen but the code does not do that the way it is. Out of three columns from sheets 'APPLES' and 'ORANGES'. I get 'APPLES' only in column A 'Combined' and 'ORANGES' only in column B of 'Combined' – Noob2Java Apr 28 '17 at 19:10
Try the edited code. Mark as accepted if it fits your needs. – CMArg Apr 28 '17 at 19:13
Ahh there we go... now it works. I am still confused by it but it works. Thanks a lot for your help and again, sorry for the confusion. – Noob2Java Apr 28 '17 at 19:16

Excel VBA copy sheet data from multiple sheets based on list

1 Answers1