Solved: Only copy selected sheets

[Gil]

Hello

I use this code to copy all sheets from several workbooks to a single workbook. It all works ok but some of the sheets being copied are unnecessary. In each original there are 3 sheets but only 1 is required to be copied to the new workbook. I need some help to copy only the named sheet required i.e.the name for example is info1.


Option Explicit

Sub TheFullRecord()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "C:\Users\Gil's\Desktop\ Log" 'Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
For Each WS In Wkb.Worksheets
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

Next WS
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

[IBihy]

Hello Gil,

Using your code, I'd do this (see stuff marked "IBY"
HTH,
Isabella

Option Explicit

Sub TheFullRecord()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "C:\Users\Gil's\Desktop\ Log" 'Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
For Each WS In Wkb.Worksheets
If WS.Name = "Info1" Then ' IBY, if that's the name you want
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If ' IBY
Next WS
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

[Gil]

Hello Isabella
That worked perfectly for me.
Many thanks
Gil

:clap: