convert ws into xlsm files

vilas desai
3StarLounger
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
HansV
Administrator
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
        wsh.Copy
        Set wbkT = ActiveWorkbook
        wbkT.SaveAs Filename:=strPath & wsh.Name & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
        wbkT.Close SaveChanges:=False
    Next wsh
    Application.ScreenUpdating = False
End Sub
Regards,
Hans