Save every worksheet as individual workbook (file format xlsm)

Dear Experts:

Below macro saves every worksheet to a new workbook. It is running fine. The only thing I cannot change is the file format to which the individal worksheets are to be saved. Below code only saves them as .xlsx. I would like to get them saved as "xlsm".

How is this done?

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Sub ExportToWorkbooks_2()
    Dim NewBook As Workbook, OldBook As Workbook, sh As Worksheet
     
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
     
    ' xlWorkbookDefault (.xlsx)
     
    Set OldBook = ThisWorkbook
     
    For Each sh In OldBook.Worksheets
        If sh.Visible = True Then
            sh.Copy
            ActiveWorkbook.SaveAs Filename:=OldBook.Path & "\" & sh.Name & "VALUES", FileFormat:=xlWorkbookDefault
            ActiveWorkbook.Close
        End If
    Next
     
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
     
End Sub

Open in new window

Andreas HermleTeam leaderAsked:
Who is Participating?
 
nutschConnect With a Mentor Commented:
You have to change the fileformat to FileFormat:=xlOpenXMLWorkbookMacroEnabled

The best way to see code often is to record a macro with what you want to do and see the resulting code.

Thomas

Sub ExportToWorkbooks_2()
    Dim NewBook As Workbook, OldBook As Workbook, sh As Worksheet
     
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
     
    ' xlWorkbookDefault (.xlsx)
     
    Set OldBook = ThisWorkbook
     
    For Each sh In OldBook.Worksheets
        If sh.Visible = True Then
            sh.Copy
            ActiveWorkbook.SaveAs Filename:=OldBook.Path & "\" & sh.Name & "VALUES", FileFormat:=xlOpenXMLWorkbookMacroEnabled
            ActiveWorkbook.Close
        End If
    Next
     
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
     
End Sub

Open in new window

0
 
Andreas HermleTeam leaderAuthor Commented:
ok, thank you very much for your professional help. Regards, Andreas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.