Shazam
10-26-2006, 06:19 AM
Hi Everyone:hi:
I got this code below from your kb's entry. But I would like to have a condition in the code like some kind of wild card. Is it possible to just only copy worksheets tabs that has the word "Incentive"? Example
If Mid(WS.Name, 9) = "Incentive" Then
WS.Copy
End If
I tried to modifief the code but know luck.
Option Explicit
Sub CombineFiles()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "C:\Production" 'Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
If Mid(WS.Name, 9) = "Incentive" Then
WS.Copy
End If
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 got this code below from your kb's entry. But I would like to have a condition in the code like some kind of wild card. Is it possible to just only copy worksheets tabs that has the word "Incentive"? Example
If Mid(WS.Name, 9) = "Incentive" Then
WS.Copy
End If
I tried to modifief the code but know luck.
Option Explicit
Sub CombineFiles()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "C:\Production" 'Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
If Mid(WS.Name, 9) = "Incentive" Then
WS.Copy
End If
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