Activate Another Workbook Not Working

[Oprichnick]

Hello I have a vba routine which was performing well, but somehow stopped working and I can't figure out why.

A have a macro in a workbook that is used to break down another workbook called "Report" (xlsx) in several excel sheets. Although there is a workbook activation, it is breaking the first file (which contain the code) which I call "Splitter".

Sub Splitting()

Dim xPath As String
xPath = Application.ActiveWorkbook.Path


Workbooks("Report.xlsx").Activate


Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xls"
    Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True




MsgBox ("The Splitting is Done")
End Sub

Thanks for your help,
Oprichnick

 

[JackDanIce]

You're not looping through the sheets of the active workbook, instead you're looping through the sheets of ThisWorkbook where the macro is being called from. Instead, try:

Sub Splitting_v1()

    Dim wks     As Worksheet
    Dim strPath As String
    
    strPath = ActiveWorkbook.path & "\@1.xls"
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    With Workbooks("Report.xlsx")
        For Each wks In .Sheets
            wks.Copy
            With ActiveWorkbook
                .SaveAs Replace(strPath, "@1", wks.Name)
                .Close False
            End With
        Next wks
    End With
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With

    MsgBox("The Splitting is Done")

End Sub

 

[Oprichnick]

Works perfectly as it should.
Thanks a lot!

 

[JackDanIce]

You're welcome