CODEWITHSUNDEEP
vba
thangvc91
thangvc91

Reputation: 333

VBA - can not save as "xlsx" from "xlsm"

Me again , I'm trying to code for spliting sheets in the xlsm file into the seperate sheet then save them in the same place with the xlsm file. the code as below:

Sub splitsheet()
    Dim path As String
    Dim cities
    ReDim cities(1 To ThisWorkbook.Worksheets.Count)
    Dim i As Long
    Dim sh As Worksheet
    path = ActiveWorkbook.path
    For i = 1 To Worksheets.Count
        cities(i) = Sheets(i).Name
        ActiveWorkbook.SaveAs _
        Filename:=path & "\" & Sheets(i).Name & ".xlsx"
        'ActiveWorkbook.Close False
    Next i

End Sub

The error in my photo below. Why it can not save as in "xlsx" extension , above code is work fine with "xlsm" extension

enter image description here

    Filename:=path & "\" & Sheets(i).Name & ".xlsm"  'it can work fine with xlsm extension

My question is how can save as in "xlsx" extension in this case. All assist/explaination will be appriciated.

Upvotes: 0

Views: 472

Answers (1)

Variatus
Variatus

Reputation: 14383

Please try this code.

Sub EachSheetToEachOwnWorkbook()
    ' 286
    
    Dim Path        As String
    Dim Ws          As Worksheet
    
    Application.ScreenUpdating = False
    Path = ThisWorkbook.Path & "\"
    For Each Ws In ThisWorkbook.Worksheets
        Ws.Copy
        With ActiveWorkbook
            .SaveAs Filename:=Path & Ws.Name & ".xlsx", _
                    FileFormat:=xlOpenXMLWorkbook, _
                    CreateBackup:=False
            .Close
        End With
    Next Ws
    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Related Questions