Create loop index and copy data from index worksheets into Master

Question

What I am trying to do is copy variable data ranges, but identical headers, from all sheets and paste into the Master sheet one after the other. The original code (CODE 1 below) renewed the data in the master whenever I clicked on another sheet and back onto the master. The problem now is that there are other sheets in the Workbook that I do not want included in the copy process.

I have edited the code I received below (CODE 2 below) to try and define start and end sheets for running a "loopindex" and also removing the "copy headers" line of code as the headers for each worksheet are appearing throughout the mastersheet. Obviously it does not work and I was wondering if someone could help.

Could you please help me correct the combined code or provide a more elegant solution? Thanks.

Original question here - Excel Forum post

Secondary code from here - Stack post LoopIndex

Original CODE 1

Private Sub Worksheet_Activate()
Dim ws As Worksheet

Application.ScreenUpdating = False
Me.UsedRange.Clear

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> Me.Name Then
    If Range("A1") = "" Then ws.Range("A1").EntireRow.Copy Me.Range("A1")'copy in the headers
    ws.UsedRange.Offset(1).Copy Me.Range("A" & Rows.Count).End(xlUp).Offset(1)'copy data
End If
Next ws

Application.ScreenUpdating = True
End Sub

Edited CODE 2

Private Sub Worksheet_Activate()
Dim ws As Worksheet

Application.ScreenUpdating = False
Me.UsedRange.Clear

Dim StartIndex, EndIndex, LoopIndex As Integer
   StartIndex = Sheets("Master sheet").Index + 1
   EndIndex = Sheets("End").Index - 1

For LoopIndex = StartIndex To EndIndex

    If Range("A1") = "" Then ws.Range("A1").Offset(1).Copy Me.Range("A" &Rows.Count).End(xlUp).Offset(1)  'copy data
Next LoopIndex

Application.ScreenUpdating = True
End Sub

Answer 1

I can just about understand why you had this as a Worksheet Activate event routine against worksheet "Master list" when there was only one source worksheet. I am having more difficulty in seeing this as convenient when you have multiple source worksheets. I am not asking you to justify your decision since I do not have a full understanding of workbook but you might like to reconsider your approach. I have coded the routine below as an normal macro but you can change this easily if you wish.

I do not like the approach of assuming the worksheets to be loaded are from Sheets("Master sheet").Index + 1 to Sheets("End").Index - 1. I would have thought that was unstable although I have never tried this approach.

I have created a hidden worksheet "Load List":

This lists the worksheets to be loaded in the sequence to be loaded.

I have filled worksheet "Sheet1" with data:

Not very imaginative data but it makes it easy to check that "Master list" is loaded with the correct data. Worksheets "Sheet2" to "Sheet5" have similar data except that the number of data rows vary and "S1" is replaced by "S2", "S3", "S4" and "S5".

After the macro has run, the top of "Master list" contains:

You can see I have loaded all rows from the first worksheet then data rows only from subsequent worksheets.

I do not say a great deal about the VBA I have used. Once you know a statement exists it is normally easy to look it up. Ask if necessary. I hope I have provided an adequate explanation of what the code does. Again ask if necessary.

Option Explicit
Sub CombinedSelected()

  Dim ColSrcMax As Long
  Dim LoadList As Variant
  Dim RowListCrnt As Long
  Dim RowListMax As Long
  Dim RowMasterNext As Long
  Dim RowSrcMax As Long

  With Worksheets("Load List")
    RowListMax = .Cells(Rows.Count, "A").End(xlUp).Row
    ' Load the values from column A of worksheet "Load List" to LoadList.
    ' The statement converts LoadList to a 2 dimensional array. It is the
    ' equivalent of Redim LoadList(1 To RowListMax, 1 to 1)
    LoadList = .Range(.Cells(1, "A"), .Cells(RowListMax, "A")).Value
  End With

  RowMasterNext = 1

  With Worksheets("Master sheet")
    .Cells.EntireRow.Delete     ' Delete existing contents
  End With

  For RowListCrnt = 2 To RowListMax
    With Worksheets(LoadList(RowListCrnt, 1))
      ' Find last used row and column containing a value.
      ' Warning.  These statements do not allow for any of the source worksheets being empty
      RowSrcMax = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
      ColSrcMax = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
      If RowListCrnt = 2 Then
        ' For first source worksheet only include header row
        .Range(.Cells(1, 1), .Cells(RowSrcMax, ColSrcMax)).Copy _
               Destination:=Worksheets("Master sheet").Cells(RowMasterNext, 1)
        RowMasterNext = RowMasterNext + RowSrcMax
      Else
        ' Data rows only to be copied
        .Range(.Cells(2, 1), .Cells(RowSrcMax, ColSrcMax)).Copy _
               Destination:=Worksheets("Master sheet").Cells(RowMasterNext, 1)
        RowMasterNext = RowMasterNext + RowSrcMax - 1
      End If
    End With
  Next

End Sub