Combining named worksheets with Macro

[Exl_in_Training]

I have this Macro that combines the contents of all sheets from the 3rd to last sheet in the work document and presenting this consolidated result in sheet that is called "Master Register", which is the second sheet in the workbook. From the Macro code below, you'll see that I pick up the first sheets to include as part of the merging exercise from Sheet 3 to last (in total 9 sheets).

However, I now need to run a report extracts from this master sheet, which means a new sheet is added to my workbook. What other way can I use to let the code pick up the first worksheet if the actual sheet number keeps changing? (Not sure if I phrased this correctly.). Regardless of how many other sheets I put in front, the data merging should always start with the sheet called Daata Sheet 3.

Sub Combine()

Dim M As Integer

On Error Resume Next
'Selecting first sheet for merging
Sheets(2).Activate
Range("A3:a2000").EntireRow.Delete

Sheets(3).Activate

Range("A3:R").EntireRow.Select

Selection.Copy Destination:=Sheets(1).Range("A3:R")

For M = 3 To Sheets.Count

Sheets(M).Activate

ActiveSheet.Range("A3", "R" & sheetlastrow).Copy

Selection.CurrentRegion.Select

Selection.Offset(2, 0).Resize(Selection.Rows.Count - 2).Select

Selection.Copy Destination:=Sheets(2).Range("A65536").End(xlUp)(2)

Sheets(2).Activate
Range("A:R").WrapText = True

Next

'Column_Width

End Sub

 

[rlv01]

Maybe something like this.

Sub Combine1()
    Dim WS As Worksheet, WSMR As Worksheet                             'master register
    Dim LastRow As Long
    Dim rng As Range, rngDest As Range

    Set WSMR = Worksheets("Master Register")
    
    WSMR.Activate
    WSMR.UsedRange.Offset(2).ClearContents

    For Each WS In Worksheets
        If UCase(Left(WS.Name, 4)) = "DATE" And WS.UsedRange.Rows.Count > 2 Then
            With WSMR
                LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
                Set rngDest = .Range("A" & .Rows.Count).End(xlUp).Offset(1)
            End With

            With WS
                Set rng = .Range("A3", .Range("A" & .Rows.Count).End(xlUp))
            End With

            rng.EntireRow.Copy rngDest
        End If
    Next WS

    With WSMR
        .Range("A:R").WrapText = True
        .UsedRange.Columns.AutoFit
    End With
End Sub

 

[John_w]

Regardless of how many other sheets I put in front, the data merging should always start with the sheet called Daata Sheet 3.

Change the loop to:

For M = Worksheets("Data Sheet 3").Index To Worksheets.Count

 

[kevin9999]

Another option

For M = Worksheets.Count - 8 To Worksheets.Count

 

[Exl_in_Training]

Thanks everyone. This one did the trick

For M = Worksheets("Data Sheet 3").Index To Worksheets.Count

Really appreciate the help