CODEWITHSUNDEEP
vbaexcel
jwoff
jwoff

Reputation: 165

Excel macro to save sheets as new macro enabled workbook

So far I have this:

Sub new_book()

    Sheets(Array("Document Data", "Invoice data", "Summary", "Invoice")).Copy
    ActiveWorkbook.SaveAs Filename:=Range("D1") & Format(Date, "ddmmyyyy") & ".xlsx", FileFormat:=52

End Sub

However I get runtime error 1004, which highlights the ActiveWorkbook line. What exactly is wrong? Thank you for your time.

Upvotes: 3

Views: 13728

Answers (3)

Ashwith Ullal
Ashwith Ullal

Reputation: 263

"SAVING FILE WITH TODAYS DATE AS NAME IN "D:\BACKUP" folder 

sub new_sheet()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("D:\BACKUP")

todays_date = Date$

For Each objFile In objFolder.Files

 If objFile.Name = todays_date & ".xls" Then
    objFile.Delete

 End If

 Next objFile



Workbooks.Add

ActiveWorkbook.SaveAs "D:\BACKUP" & todays_date & ".xls"    
end sub

Upvotes: -1

user4039065
user4039065

Reputation:

Your Workbook.SaveAs method is using the correct FileFormat parameter (xlOpenXMLWorkbookMacroEnabled = 52) but you are inexplicably trying to append the filename with a hard-coded .xlsx file extension rather than an .xlsm file extension. In any event, the correct extension will be added if you do not supply it.

Sub new_book()

    Sheets(Array("Document Data", "Invoice data", "Summary", "Invoice")).Copy
    ActiveWorkbook.SaveAs Filename:=Range("D1") & Format(Date, "ddmmyyyy") , FileFormat:=52

End Sub

I'm a little uncomfortable with the use of Range("D1") without specifying a parent worksheet. Be very careful about not attempting to use restricted characters in the filename.

Upvotes: 10

R.Katnaan
R.Katnaan

Reputation: 2526

Try as follow:

ActiveWorkbook.SaveAs Filename:=Range("D1") & Format(Date, "ddmmyyyy"), FileFormat:=52

Upvotes: 3

Related Questions