Export Excel Workbook in .xls Format

Question

I have an Excel that gets updated with sales numbers daily. At the end of the week, when the Excel is complete, I export a PDF copy of the WEEKLY worksheet. Once I have a PDF copy, the sales numbers are transferred to another sheet within the workbook, emptying the WEEKLY worksheet.

In addition to this PDF copy of the WEEKLY worksheet, I'd like to export the entire workbook in a separate Excel file to the same location (.xls format is fine). I'd like to do this before emptying the WEEKLY worksheet. I've tried using a save as macro, but I want to remain in my original Excel - not the newly saved file.

For reference, here's the VBA code for my PDF export:

Sub SaveWeekly()
'
' SaveWeekly Macro
'

'
    Sheets("WEEKLY").Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "Z:\Excel New\Previous Excels\" & Range("A1") & " " & Range("H1") & ", " & Format(Date, "yyyy") & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

End Sub

Any help is appreciated.

I feel like this is a simple question of order. If you don't want to quit the file you're working in (you've got additional work to do), but want to save the file in a separate locale before quitting... why not use SaveAs as the last step to archive the file once all your work is done? — gravity
I edited my question to more accurately describe the reason why I needed to export, or rather, copy. Thank you everyone for your help! — John Engelman

YasserKhalil YasserKhalil · Accepted Answer · 2018-08-10T17:20:23

Try this code

Sub Test()
Dim strDate As String
Dim strTime As String

strDate = Format(Date, "DD-MM-YYYY")
strTime = Format(Time, "hh.mm.ss")

Application.DisplayAlerts = False
    With ActiveWorkbook
        .SaveCopyAs fileName:=ThisWorkbook.Path & "\" & strDate & "_" & strTime & "_" & .Name
    End With
Application.DisplayAlerts = True
End Sub

Export Excel Workbook in .xls Format

1 Answers