Consolidate sheets

[Portucale]

Hi,

I am looking to consolidate only sheets which have the same within the same name like (Test;Test(1); Test(2); etc.) within the same workbook, I've found different codes but they consolidate ALL the worksheets.

Appreciated if someone could point me in the right direction,

Sample of a code, but for some reason is not working!!

Sub Transform()
' to consolidate any tabs within the workbook with same name

Dim wkb As Workbook
Dim ws As Worksheet
Dim strTarget As String
Dim strSourceSheet As String
Dim intLastCol As Integer
Dim longLastRow As Long

For Each ws In ThisWorkbook.Worksheets

    If Right(ws.Name, 1) <> ")" Then
        strTarget = ws.Name
   
    ElseIf Right(ws.Name, 1) = ")" Then
        strSourceSheet = ws.Name
       
        Sheets(strTarget).Select
        intLastCol = Cells(1, 256).End(xlToLeft).Column
       
        Sheets(strSourceSheet).Select
        longLastRow = Cells(1048576, 1).End(xlUp).Row
        Range(Cells(1, 1), Cells(longLastRow, intLastCol)).Copy
       
        Sheets(strTarget).Select
        longLastRow = Cells(1048576, 1).End(xlUp).Row
        Range("a" & longLastRow + 1).Select
        ActiveSheet.Paste
        Application.DisplayAlerts = False
        Worksheets(strSourceSheet).Delete
        Application.DisplayAlerts = True
       
    End If

Next ws
End Sub

Thanks in advance,

 

[Portucale]

Hi,

It seems that I found the issue/solution, just changed
ForEach ws In ThisWorkbook.Worksheets
to
For Each ws In wkbTemp.Worksheets

as the macro is to run in a different Workbook from where the Macro is stored...

Thanks guys,

 

[Monty]

Hello Portucale

Try this

This is just a sample with which you can ammend..But it would be great if you can provide a test file

Sub test()
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name Like "Test*" Then
    'Copy text
    'Paste in master sheet
   
    End If
Next ws

End Sub

 

[Monty]

Please let me know any questions...Happy to help you!