Shazam
01-22-2008, 10:20 AM
Hi everyone,
I would like to modified this code below. Right now its combinding multiple workbooks with a specific file path. But I would like to choose the path manually. So I thought by change this line below:
Path = "Z:\Foundry Performance\0 - Archives\Core Incentive\2007\"
To this
FilesToOpen = Application.GetOpenFilename(FileFilter:= _
"Excel files (*.xls), *.xls", MultiSelect:=True)
But it does not work. It only open and closes the files. Is there a way to modifed it to work?
Option Explicit
Sub CombineFiles()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Dim FilesToOpen
'Application.ScreenUpdating = False
Path = "Z:\Foundry Performance\0 - Archives\Core Incentive\2007\" '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 InStr(1, WS.Name, "Incentive") <> 0 Then
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next WS
Wkb.Close False
FileName = Dir()
Loop
End Sub
I would like to modified this code below. Right now its combinding multiple workbooks with a specific file path. But I would like to choose the path manually. So I thought by change this line below:
Path = "Z:\Foundry Performance\0 - Archives\Core Incentive\2007\"
To this
FilesToOpen = Application.GetOpenFilename(FileFilter:= _
"Excel files (*.xls), *.xls", MultiSelect:=True)
But it does not work. It only open and closes the files. Is there a way to modifed it to work?
Option Explicit
Sub CombineFiles()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Dim FilesToOpen
'Application.ScreenUpdating = False
Path = "Z:\Foundry Performance\0 - Archives\Core Incentive\2007\" '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 InStr(1, WS.Name, "Incentive") <> 0 Then
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next WS
Wkb.Close False
FileName = Dir()
Loop
End Sub