Split workbook into multiple files based on value in cell

[jmcdoe]

Hi,

I am trying to find a way to split a workbook with multiple sheets based on the value in cell A1.

Quick overview of my workbook, cell A1 has the store name, and the worksheet contains 2 reports for each store that I have. So essentially:
Sheet1 = Store1 ReportA
Sheet2 = Store1 ReportB
Sheet3 = Store2 ReportA
Sheet4 = Store2 ReportB
and so on.

I would like to split the workbook into multiple work books based on the store name (value of A1) and automatically save them to a folder. So one file for Store1 with its reports, another for Store2 with its reports, an so on.

Also, the sheet names are like the example above if using value in A1 is not an option.

Can someone please help on this, or possibly direct me to a solution for this?

Thanks.

 

[John_w]

Welcome to MrExcel forums.

Try this (change the destination folder in the code).

Public Sub Save_Store_Sheet_Pairs()

    Dim destinationFolder As String
    Dim i As Integer
    Dim storeName As String
    
    destinationFolder = "C:\path\to\folder\"        'CHANGE FOLDER
    If Right(destinationFolder, 1) <> "\" Then destinationFolder = destinationFolder & "\"
    
    For i = 1 To Worksheets.Count Step 2
        storeName = Worksheets(i).Range("A1").Value
        Worksheets(Array(Worksheets(i).Name, Worksheets(i + 1).Name)).Copy
        ActiveWorkbook.SaveAs destinationFolder & storeName & ".xlsx"
        ActiveWorkbook.Close SaveChanges:=False
    Next

End Sub