Method 'SaveAs' of object '_Workbook" failed Run-time Error 1004

[2clacaze]

Sub SaveWOWithNewName()


Dim strNew As String
Dim F6 As Range

strNew = "C:\Users\AdminAssist\Dropbox\Work Orders\Work Order Index\@1.xlsm"

Application.ScreenUpdating = False

With ActiveSheet
Set F6 = .Range("F6")
.Copy
End With

ActiveWorkbook.SaveAs Replace(strNew, "@1", F6.Value), FileFormat:=xlOpenXMLWorkbookMacroEnabled
With ActiveSheet
F6.Value = F6.Value + 1
.Range("A9:G19,B4:B7,E20,E48,F7").ClearContents
End With

Application.ScreenUpdating = True

Set F6 = Nothing

End Sub

I get the error message "Run-time Error 1004: Method 'SaveAs' of object '_Workbook' failed. Debugger takes me to bold line above. Please Help...So Close!

 

[wideboydixon]

What's in F6?

 

[2clacaze]

P.O. number that i need to increase with each open workbook

 

[Trebor76]

Hi 2clacaze,

We need to know what the code is attempting to name the new file. Let us know what appears in the following message box...

MsgBox Replace(strNew, "@1", F6.Value)

...after you copy it immediately before this line of code:

ActiveWorkbook.SaveAs Replace(strNew, "@1", F6.Value), FileFormat:=xlOpenXMLWorkbookMacroEnabled

Robert

 

[2clacaze]

MsgBox says: 'C:\Users\AdminAssist\Dropbox\Work Orders\Work Order Idex\ .xlsm'

Which is almost there. I tried to establish the save name through the F6, increasing the Work Order number in that cell by 1 each time it was opened in an active copy. Also would name the file after said Work Order Number in the F6 cell. If the value of F6 in the open copy is 172994, I want the worksheet named '172994' the filepath to be

'C:\Users\AdminAssist\...\Work Order Index\172994.xlsm'

and then when I run the macro for a new worksheet, I want it to save the current file, print 3 copies, and open a new macro-enabled workbook named '172995' and save at

'C:\Users\AdminAssist\...Work Order Index\172995.xlsm'

and so on. Am I asking too much of the VBA system? I can settle with no printing and merely saving one and opening the sheets in order so I don't have to remember which is next.

 

[wideboydixon]

The output from the message box suggests that F6.Value is returning an empty string. You should double-check that you've got a reference to the correct cell.

WBD

 

[2clacaze]

Positive. Trying to send photos of screen. Can't even figure that out. F6 currently has a value of '999997'

 

[2clacaze]

i had actually double checked that since my first run at it had this information in F5, I had to modify my macro after I altered the worksheet.

 

[Trebor76]

i had actually double checked that since my first run at it had this information in F5, I had to modify my macro after I altered the worksheet.

So it's all sorted now? Maybe worth naming the cell and referencing that named range in your code instead of a particular cell.

 

[Trebor76]

i had actually double checked that since my first run at it had this information in F5, I had to modify my macro after I altered the worksheet.

So it's all sorted now? Maybe worth naming the cell and referencing that named range in your code instead of a particular cell.