Run time error 1004 - Method 'SaveAs' of object _workbook failed

[dannyboy1982]

Hi,

I have the following code that I have been using for a few days with no problem, and today I started getting 'Run time 1004' error messages.

I am creating a new workbook, combining the contents of cell B1 and Y2 to create a file name and saving it as a .xlsm file.

Code:

Sub NewWB()
Dim name1 As String
Dim name2 As String

name1 = ActiveWorkbook.Sheets(1).Range("B1").Value
name2 = ActiveWorkbook.Sheets(1).Range("Y2").Value

Application.ScreenUpdating = False
Workbooks.Add
'Saving the Workbook
ActiveWorkbook.SaveAs "C:\DR\Cp and Cpk\" & name1 & " " & name2 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Workbooks("SPC V1.xlsm").Sheets("Job dimensions").Activate
Call data
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

Any help with this would be greatly appreciated.

Regards

DR

 

[Dryver14]

Not sure this is the issue as you have used it before but I wouldn't have thought you need the " before name1

ActiveWorkbook.SaveAs "C:\DR\Cp and Cpk\" name1 & " " & name2 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

 

[pgc01]

Hi

One of the reasons would be an invalid name

Before the saving check the value of the name:

MsgBox "C:\DR\Cp and Cpk\" & name1 & " " & name2 & ".xlsm"

What do you get (when the saving fires the error)?

 

[dannyboy1982]

Hi, by adding in this line, I get a msgbox saying C:\DR\Cp and Cpk\ .xlsm, which appears to be the problem, vba is not pulling data from B1 and Y2 to create the file name. But nothing has changed since it was working. Are name1 and name2 defined correctly (dim as string)?

 

[dannyboy1982]

I have removed the activesheet line where name1 and name2 were defined and added in the specific location, i.e. Workbook, sheet and cell range and now it appears to be working.

name1 = Workbooks("SPC V1.xlsm").Sheets("Job dimensions").Range("B1").Value
name2 = Workbooks("SPC V1.xlsm").Sheets("Job dimensions").Range("Y2").Value

instead of:

name1 = ActiveWorkbook.Sheets(1).Range("B1").Value name2 = ActiveWorkbook.Sheets(1).Range("Y2").Value

 

[pgc01]

I'm glad you figured it out and it's working now.