convert ws into xlsm files

vilas desai
Posts: 252
Joined: 16 Mar 2011, 09:33

convert ws into xlsm files

Post by vilas desai »

Dear Experts.
Hope you are all safe and healthy.
I need help to convert worksheets into .xlsm files with the same names
ex ws name: Index, File name: Index.xlsm
The converted files should be saved in a folder on desktop named "Convert""
There would be about 75 such ws in a workbook
The working file is attached
Thanks and best regards
Vilas Desai
You do not have the required permissions to view the files attached to this post.

User avatar
Posts: 71216
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: convert ws into xlsm files

Post by HansV »

Here is a macro:

Code: Select all

Sub SaveSheets()
    Dim strPath As String
    Dim wbkS As Workbook
    Dim wsh As Worksheet
    Dim wbkT As Workbook
    Application.ScreenUpdating = False
    strPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Convert\"
    Set wbkS = ActiveWorkbook
    For Each wsh In wbkS.Worksheets
        Set wbkT = ActiveWorkbook
        wbkT.SaveAs Filename:=strPath & wsh.Name & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
        wbkT.Close SaveChanges:=False
    Next wsh
    Application.ScreenUpdating = False
End Sub