I have the following macro, taken from another forum, that will copy the same worksheet from multiple workbooks into one workbook.
Sub CombineSheets()
Dim sPath As String
Dim sFname As String
Dim wBk As Workbook
Dim wSht As Variant
Application.EnableEvents = False
Application.ScreenUpdating = False
sPath = InputBox("Enter a full path to workbooks")
ChDir sPath
sFname = InputBox("*")
sFname = Dir(sPath & "\" & sFname & ".xlsx*", vbNormal)
wSht = InputBox("Enter a worksheet name to copy")
Do Until sFname = ""
Set wBk = Workbooks.Open(sFname)
Windows(sFname).Activate
Sheets(wSht).Copy Before:=ThisWorkbook.Sheets(1)
wBk.Close False
sFname = Dir()
Loop
ActiveWorkbook.Save
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
I am using * as a wildcard in the filepath, the code keeps wrroring on Set wBk = Workbooks.Open(sFname). Its find the first file in the folder, even mentions the name of the file, but says it cant be found, yet it finds it and knows the name.
It doesnt matter if I give the actual name of the workbook instead of *, it says it cant find it (I have comfimred its there and I can open it)
Sub CombineSheets()
Dim sPath As String
Dim sFname As String
Dim wBk As Workbook
Dim wSht As Variant
Application.EnableEvents = False
Application.ScreenUpdating = False
sPath = InputBox("Enter a full path to workbooks")
ChDir sPath
sFname = InputBox("*")
sFname = Dir(sPath & "\" & sFname & ".xlsx*", vbNormal)
wSht = InputBox("Enter a worksheet name to copy")
Do Until sFname = ""
Set wBk = Workbooks.Open(sFname)
Windows(sFname).Activate
Sheets(wSht).Copy Before:=ThisWorkbook.Sheets(1)
wBk.Close False
sFname = Dir()
Loop
ActiveWorkbook.Save
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
I am using * as a wildcard in the filepath, the code keeps wrroring on Set wBk = Workbooks.Open(sFname). Its find the first file in the folder, even mentions the name of the file, but says it cant be found, yet it finds it and knows the name.
It doesnt matter if I give the actual name of the workbook instead of *, it says it cant find it (I have comfimred its there and I can open it)