Run-time error '1004'

[waldymar]

Hi All,
I have the following code

Sub Automatization()


Dim twb As Workbook, wb As Workbook, new_sheet_counter As Integer


Application.ScreenUpdating = False
    Set twb = ThisWorkbook
    


    new_sheet_counter = Application.SheetsInNewWorkbook
    Set wb = Workbooks.Add
'----- Copy relevant worksheets to new book -----
    twb.Sheets(Array("DashboardTotal", "DashboardMT", "DashboardPT")).Copy After:=wb.Sheets(new_sheet_counter)
'----- Remove original worksheets and set others to values-only -----
    Application.DisplayAlerts = False
    With wb
        For counter = 1 To new_sheet_counter
            .Sheets("Sheet" & counter).Delete
        Next
        For Each ws In wb.Worksheets
            ws.Cells.Copy
            ws.Cells.PasteSpecial (xlPasteValues)
            Application.CutCopyMode = False
        Next
        wb.Sheets("DashboardTotal").Select
    End With
'----- Set up variables for saving report -----
    reportingdate = Format(Date, "dd-mmm-yyyy")
    reportingtime = Format(Time, "hh-mm")
    outputlocation = coding.Range("b1")
    If Right(outputlocation, 1) <> "\" Then outputlocation = outputlocation & "\"
    vsion = 1
'----- Save new workbook with version control -----
    wb.SaveAs Filename:=outputlocation & "Daily Report-" & reportingdate & "-" & reportingtime & ".xlsx"
    wb.Close False
    Application.DisplayAlerts = True
Application.ScreenUpdating = True
    
End Sub

The code stops working on this line:

wb.SaveAs Filename:=outputlocation & "Daily Report-" & reportingdate & "-" & reportingtime & ".xlsx"

Can anyone spot a bug?

Thanks

 

[waldymar]

coding is a worksheet in the current workbook (not the added one). shall I write the whole path like: Workbooks("x").sheets(coding).range("a1")?

 

[Norie]

If there was a problem with the worksheet the error would happen earlier in the code when you try to refer to the workbook.<br><br> Have you checked the values of the variables when the code runs?

 

[waldymar]

If there was a problem with the worksheet the error would happen earlier in the code when you try to refer to the workbook.

Have you checked the values of the variables when the code runs?

Yes, values are good. You, this code 1 time works and another blocks ... Anyway, thank you so much

 

[chuckchuckit]

If you are starting your macro with a Ctrl-Shift combo, try removing the Shft key. Instead of Ctrl-Shift-R, try Ctrl-R instead.

There is a bug in MS that will "randomly" stop a macro when opening a workbook when Shift key was used. Likely a side effect from Excel feature of holding down the Shift key when starting a macro to stop the Autpen macro from running. MS admits the bug. And I found this to be a random perplexing bug until I removed the Shift key from all programs having anything to do with workbooks handling.

A test: If you launch your macro from the Menu, View Macro, Run and it work ok, then using the Shift key as part of a macro quick key combo is what is causing the problem.

 

[waldymar]

I launch macro through the button.

 

[chuckchuckit]

Is it a userform button? If so and you start the original Excel program with a Ctrl-Shift key combiniation, you can still be causing the problem with the shift key.

How are you starting the original opening Excel program?

 

[waldymar]

Is it a userform button? If so and you start the original Excel program with a Ctrl-Shift key combiniation, you can still be causing the problem with the shift key.

How are you starting the original opening Excel program?

The original file is opened manually. The new workbook is opened through the VBA code as you can see. What do you mean by Ctrl-Shift key combination? I'm not using it ...
The button for macro comes from Developer tab->Controls->Insert->Button (Form Control).

 

[chuckchuckit]

Macros can be started with quick key combinations, such as Ctrl-Shift-R instead of clicking a button. They are setup by clicking your menu View tab, then click Macros, then click View Macros. All your sub routines will be there. If you highlight your "Sub Automization" then click Options, a box will pop up that shows "Ctrl +" if you then push your Shift key and then another key like "R" your macro will then be setup to start with "Ctrl-Shift-R" keys combination instead of clicking like you do now to start the macro.

If you have no "Shift" key in that combination Option for your "Sub Automization" then you do not have the "Shift" key problem I have stated. But many people use those Shift key combinations to quick start Macro's. The run-time error 1004 often happens when using the Shift key there when opening /handling workbooks sometime later after their macro is running. It is a MS bug using the Shift key in those cases.

 

[waldymar]

Got it, thanks!

 

[chuckchuckit]

You're welcome.