Runtime error 1004 by opening other workbooks

[davidov]

I have the following VBA code :

Sub CombineSheets() Dim sourceSheet As Worksheet 'start of macro define starting sheet
Set sourceSheet = ActiveSheet 'start of macro define starting sheet

Dim sPath As String
Dim sFname As String
Dim wBk As Workbook
Dim wSht As Variant


Application.EnableEvents = False
Application.ScreenUpdating = False
sPath = "H:\lists"
'sPath = InputBox ("Enter a full path to workbooks")
ChDir sPath
'sFname = InputBox("Enter a filename pattern")
sFname = "10*"
sFname = Dir(sPath & "" & sFname & ".xl*", vbNormal)
'wSht = InputBox("Enter a worksheet name to copy")
wSht = "Sheet1"
Do Until sFname = ""
Set wBk = Workbooks.Open(sFname)
Windows(sFname).Activate
Sheets(wSht).Copy After:=ThisWorkbook.Sheets(1)
wBk.Close False
sFname = Dir()
Loop
ActiveWorkbook.Save
Application.EnableEvents = True
Application.ScreenUpdating = True

'At the end of macro back to starting sheet
Call sourceSheet.Activate
End Sub

Originally it had inputboxes but as this is standard I edited in such way that it would go to the right location and files automatically.

I found this VBA code somewhere online and I use it to import sheets (sheet1) of several workbooks in the same workbook (and then I merge them, remove duplicates etc with other macros)

I also put in some code to have me return to my start sheet from where I started.

But as soon as I added code to bring me back to starting sheet I get Runtime error 1004
In the code I want Sheet1 copied into my workbook from workbooks in a specific folder that start their name with 10. One file is called 1011.xlsx. Now it says: "Sorry we couldn't find 1011.xlsx. is it possible it was moved, renamed or deleted?"

This is not the case, in fact it can come up with the entire name of the workbook so it can find it. The workbook is not protected or a read only in any way.

Also the very first time I run the macro it works, it just doesn't work anymore when running it a second time. So I assume it is because of the added code, yet I don't know why that causes this runtime error.

Can it be fixed without removing the code to return to starting sheet (changing it and do it in another way is fine)?

 

[Fluff]

Remove the word Call from the penultimate line

 

[davidov]

Remove the word Call from the penultimate line

Unfortunately that didn't work. Thanks for the try

 

[Fluff]

Does it actually open any workbooks?

 

[davidov]

It only will acces the workbook, but even the original code will not open the workbook as you would do yourself.

 

[Fluff]

In that case make this change

sPath = "H:\lists[COLOR=#ff0000]\[/COLOR]"