Share via

Getting run-time error '1004': document not saved error when trying to save and create new sheet number. Can someone please help?!

Anonymous
2022-05-18T11:08:52+00:00

this is the vba code that is pulling the error highlighted

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. OssieMac 47,981 Reputation points Volunteer Moderator
    2022-05-18T11:19:32+00:00

    You are assigning xlsx extension to the file name variable and trying to save a workbook with vba code that needs to be xlsm extension.

    Also, the file format for macro enabled workbook is FileFormat:=xlOpenXMLWorkbookMacroEnabled

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-05-18T11:41:56+00:00

    Would this be correct then?

    Sub PostToRegister() 

    Dim WS1 As Worksheet 

Dim WS2 As Worksheet 

Set WS1 = Worksheets("PCSheet") 

Set WS2 = Worksheets("Register") 

'Figure out which row is the next row' 

nextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1 

'Write the important values to the register' 

WS2.Cells(nextRow, 1).Resize(1, 5).Value = Array(WS1.Range("G4"), WS1.Range("F1"), WS1.Range("G3"), WS1.Range("G5"), WS1.Range("H56"))

    End Sub

    Sub NextPCSheet() 

    Range("C10").Value = Range("C10").Value + 1 

Range("B16:G54").ClearContents 

Range("L33:M57").ClearContents 

Range("G4:H4").ClearContents 

Range("C2:C3").ClearContents

    End Sub

    Sub SavePCSheetWithNewName() 

    Dim NewFN As Variant 

PostToRegister 

ActiveSheet.Copy 

NewFN = "M:\Powder Coat\Supermarket saved PC sheets" & Range("F1").Value & ".xlsm" 

ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled 

ActiveWorkbook.Close 

NextPCSheet

    End Sub

    0 comments