VBA Code to only select a specific tab

[stanich]

hi,

i am trying to create a macro that copies a specific tab from multiple workbooks into one single workbook. The macro below seems to copy all tabs though.

Is there a way to change it so that it only copies a tab (which is present in all workbooks) called "1.GOP"?

Sub CONSOL()
Dim Path As String
Dim Filename As String
Dim Sheet As Worksheet

Application.ScreenUpdating = False

Path = "C:\Users\username\Desktop\test"
Filename = Dir(Path & "*.xlsx")

Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop

Application.ScreenUpdating = True

End Sub



thank you

 

[Fluff]

Change this

For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet

to

ActiveWorkbook.Sheets("1.GOP").Copy After:=ThisWorkbook.Sheets(1)

 

[stanich]

thank you, works like a charm!

 

[Fluff]

You're welcome & thanks for the feedback.