Gil
02-14-2011, 12:29 PM
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
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