split and save individual tabs

Hello,

I have been piecing together the below macro to walk through a file, paste over as values and save off each tab as its own file. It works exactly as I had hoped when I was testing it out in a generic file but when I bring it in with my live data, it is no longer working. I'm getting a debug error and it's highlighting the ActiveSheet.Select after the paste special lines. Any idea what could be causing this and is there a better way for me to do it?

Thank you in advance for any help.

Tristan

Sub SplitWorkbook()
'
' SplitWorkbook Macro
'

'
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    ActiveSheet.Select
    ActiveSheet.Copy
    Dim FileName As String
    Dim Path As String
    Application.DisplayAlerts = False
    Path = "C:\Users\jsmith\OneDrive - Test\Documents\Model\2022 Budget\Partners\"
    FileName = Range("A1").Value & ".xlsx"
    ActiveWorkbook.saveas Path & FileName, xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    ActiveWorkbook.Close
    Next ws

End Sub

TestSplit.xlsx

In general, there is no need to select or activate objects, so a corresponding run-time error is easily avoided in this regard.
Try the code below and see if it does what you want.

Sub SplitWorkbook()

    Dim ShtSource As Worksheet, ShtDestination As Worksheet

    For Each ShtSource In ThisWorkbook.Worksheets

        ShtSource.Copy
        Set ShtDestination = ActiveSheet
        With ShtDestination.UsedRange
            .Copy
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With
        Excel.Application.CutCopyMode = False
        Excel.Application.Goto Range("A1")

        Dim FileName As String
        Dim Path As String
        Excel.Application.DisplayAlerts = False
        Path = "C:\Users\jsmith\OneDrive - Test\Documents\Model\2022 Budget\Partners\"
        With ShtDestination
            FileName = .Range("A1").Value & ".xlsx"
            .Parent.SaveAs Path & FileName, xlOpenXMLWorkbook
            .Parent.Close
        End With
        Excel.Application.DisplayAlerts = True

    Next ShtSource
End Sub

I see you have a suggestion already.
Since I was working on it, I might as well give mine also.

Sub Maybe_So()
Dim Path As String, ws As Worksheet
Path = "C:\Users\jsmith\OneDrive - Test\Documents\Model\2022 Budget\Partners\"
    For Each ws In ThisWorkbook.Worksheets
    ws.Copy
        With ActiveWorkbook
            .Sheets(1).UsedRange.Value = .Sheets(1).UsedRange.Value
                .SaveAs Path & .Sheets(1).Range("A1").Value & ".xlsx", FileFormat:=51
            .Close
        End With
    Next ws
End Sub

Thank you all for your quick responses.

What I meant by live date is that I'm working with a file that has links to TM1 to pull financial data, I obviously should have mentioned that above. Upon further investigation, it sounds like that could be part of my problem. Unfortunately neither of the above solutions are working. I stepped through both and it doesn't look like they're actually copying the data over to the new tab which is I think what's causing the error when it tries to save because the filename is pulling from whatever values are in A1.

I'm currently playing around with making a copy of my file and then using the TM1 snapshot function to break the links in the hopes that breaking the connection with TM1 might make a difference.

Let me know if you have any other ideas.

Thank you again.

Thank you all again for your help,

Going on the assumption that TM1 was the cause of my issue, I updated the file, broke the links using the snapshot function, saved the file as a copy and then disconnected TM1. I then reopened the file and ran my original macro and it worked.

Enjoy the rest of the we

It would seem that I spoke to soon. It seems to work intermittently. Going to continue to troubleshoot in the hopes that I can isolate what's causing the issue.

I think it's the TM1 connection. I opened the VBA editor and there's an object "Sheet1 (Cognos_Office_Connection_Cache)". I'm going to try breaking the links and then bring what I need into a fresh file without that and then run the macro.

Glad to help & thanks for the follow-up. If you're getting stuck, do let us know.

Thank you,

It definitely seems like the issue is the link with TM1. I took more of a brute force approach and it seems to work. I first ran

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Next ws

to break all links in the entire file. I then tried my initial macro with no success. I then highlighted multiple tabs and did a move/copy to new book to create a fresh workbook with absolutely no TM1 connection, reran my initial macro and it worked.

So far, all signs point to the TM1 connection being the issue.