Shazam
11-13-2006, 02:21 PM
Hi everyone:hi:
I have this code below that will combined files into one workbook.
Sub CombineFiles()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Application.ScreenUpdating = False
Path = "C:\Test" '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
Wkb.Close False
FileName = Dir()
Loop
Sheets("Sheet1").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
But I would like to open the files that are listed in column A. How can the code below be combined with the code above.
Dim R As Range
For Each R In Range("A1", Range("A65535").End(xlUp))
Workbooks.Open ("C:\Test" & R.Value)
Next R
I have this code below that will combined files into one workbook.
Sub CombineFiles()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Application.ScreenUpdating = False
Path = "C:\Test" '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
Wkb.Close False
FileName = Dir()
Loop
Sheets("Sheet1").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
But I would like to open the files that are listed in column A. How can the code below be combined with the code above.
Dim R As Range
For Each R In Range("A1", Range("A65535").End(xlUp))
Workbooks.Open ("C:\Test" & R.Value)
Next R