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

 

[Norie]

Have you checked the valeus of outputlocation, reportingdate and reportingtime?

 

[adam087]

It probably won't make any difference but I'd always stick 'Option Explicit' at the top and explicitly declare those three variables so you can be certain you're passing text strings.

Option Explicit

Sub Automatization()

Dim twb As Workbook, wb As Workbook, new_sheet_counter As Integer
Dim reportingdate, reportingtime, outputlocation as String

Application.ScreenUpdating = False
    Set twb = ThisWorkbook
...

Regards
Adam

 

[waldymar]

Have you checked the valeus of outputlocation, reportingdate and reportingtime?

all those name have correct names

 

[Norie]

What's the actual error message?

 

[waldymar]

Adam, you are right, I added Dim properties!
Personally, I have concerns about this line:

outputlocation = coding.Range("b1")

Maybe it doesn't understand in which workbook shall it look for "coding" worksheet?! Any ideas?

 

[waldymar]

Full error message is:

Run-time error '1004'

Document not saved.

 

[Norie]

Adam, you are right, I added Dim properties!
Personally, I have concerns about this line:

outputlocation = coding.Range("b1")

Maybe it doesn't understand in which workbook shall it look for "coding" worksheet?! Any ideas?

I thought the variables all had the correct values? Which workbook should the code be looking at.<br><br>Is there any other code?<br><br> PS If you want to dim all the variables as string you need to use something like this.

Dim reportingdate As String, reportingtime As String, outputlocation As String

 

[waldymar]

I thought the variables all had the correct values? Which workbook should the code be looking at.

Is there any other code?

PS If you want to dim all the variables as string you need to use something like this.

Dim reportingdate As String, reportingtime As String, outputlocation As String

There is no other code.
Worksheet 'coding' refers to the current workbook (not the added one).

Set twb = ThisWorkbook

Maybe later, when a new workbook is added it becomes 'ThisWorkbook'?
Thanks for correcting string properties

 

[Norie]

I meant worksheet, not workbook. Sorry.