Splitting Workbook into multiple books

This is an extensive code to do it for you from end to end - so i have just one question for you - does the format of the Original (sample) file match your real file? Are the columns and headings in the same place in each tab?

If your sample files do not match your original files, there will be a lot of re-work, so i am asking you this first itself.

Perhaps this might give you a start.

Sub test()
Dim wbSrc As Workbook
Dim wbNew As Workbook
Dim rngSite As Range

    Set wbSrc = ThisWorkbook
    
    Set rngSite = wbSrc.Worksheets("Sites").Range("B3")
    
    While rngSite.Value <> ""
    
        wbSrc.Worksheets(Array("Data", "Consol", rngSite.Value)).Copy
        Set wbNew = ActiveWorkbook
        
        ' TODO - manipulate data in new workbook so it only contains data relevant to the site

        wbNew.SaveAs ThisWorkbook.Path & Application.PathSeparator & rngSite.Value & rngSite.Offset(, 1).Value
        
        Set rngSite = rngSite.Offset(1)
    Wend
End Sub

All it does right now is create a workbook for each site with the 'Data', 'Consol' and relevant site worksheets.

It then saves the new workbook in the same directory as the original file.

Nori, Thanks. This is what I have so far:

Sub test()
Dim wbSrc As Workbook
Dim wbNew As Workbook
Dim rngSite As Range
Dim rngSiteNo As String
Dim Period As Range
Dim Datasite As Range

    Set wbSrc = ThisWorkbook
    
    Set rngSite = wbSrc.Worksheets("Sites").Range("B3")
    rngSiteNo = rngSite.Offset(0, 1).Value

    Set Period = wbSrc.Worksheets("Consol").Range("B1")
    
    While rngSite.Value <> ""
' Create new workbook for site
    
        wbSrc.Worksheets(Array("Consol", "Data", rngSite.Value)).Copy
        Set wbNew = ActiveWorkbook
       
' Manipulate data in new workbook so it only contains data relevant to the site
       ' Delete unwanted columns from P&L_Map
        Range("c4").Select
       Do While ActiveCell.Value <> ""
            If ActiveCell.Value = rngSiteNo Then
            ActiveCell.Offset(0, 1).Select
            Else
            Selection.EntireColumn.Delete
            End If
     Loop
    ' Delete unwanted entries from Tranasactions list
    
    Sheets("Data").Select
        Range("I2").Select
        Do While ActiveCell.Value <> ""
            If ActiveCell.Value = rngSiteNo Then
            ActiveCell.Offset(1, 0).Select
            Else
            Selection.EntireRow.Delete
            End If
     Loop
        

        wbNew.SaveAs ThisWorkbook.Path & Application.PathSeparator & rngSite.Value & Period.Value
        
'Select next site
        
        Set rngSite = rngSite.Offset(1)
        rngSiteNo = rngSite.Offset(0, 1).Value
    Wend
End Sub

The data list has 13,000 transactions in it and so deleting the unwanted transactions in it line by line takes a long time, especially if I have to do it 30 times (once for each site). Is there a quicker way to do this? Would switching off 'screen updating' (not sure this is the correct phrase) help?

Uther,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

Uther

I posted in a bit of a hurry and now I've had a think about it you probably don't want to copy 'Data' and 'Consol' to the new workbook.

What would be better would be to copy the site worksheet to create the new workbook.

Then add 2 new workbooks and populate them with the relevant data from the original 'Data' and 'Consol' worksheets.

To get what's needed from 'Data' a filter can be used and to get it from 'Consol' a Match function can be used to find the column to copy.