Runtime error 1004 by opening other workbooks

davidov

New Member
Joined
Aug 1, 2017
Messages
10
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)?
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
Remove the word Call from the penultimate line
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
Does it actually open any workbooks?
 

davidov

New Member
Joined
Aug 1, 2017
Messages
10
It only will acces the workbook, but even the original code will not open the workbook as you would do yourself.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
In that case make this change
Code:
sPath = "H:\lists[COLOR=#ff0000]\[/COLOR]"
 

Watch MrExcel Video

Forum statistics

Threads
1,123,381
Messages
5,601,302
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top