Split a large spreadsheet down into multiple files (by column)

Question

Hi,

I am looking for advice on the best / quickest way to split a large spreadsheet down into multiple files. I have my data relating to different sales advisors, arranged in separate columns. I now need to send them just their data so I’ll like to find a way of creating a spreadsheet/separate file containing columns 1,2,3 then 1,2,4 then 1,2,5 and so on.

Any help would be greatly appreciated.

Answer 1

Here is a macro you can run:

Sub SplitSheet()

    Dim strPath As String

    Dim wshS As Worksheet

    Dim wbkT As Workbook

    Dim wshT As Worksheet

    Dim c As Long

    Dim n As Long

    Application.ScreenUpdating = False

    strPath = ActiveWorkbook.Path

    Set wshS = ActiveSheet

    n = wshS.Cells(1, wshS.Columns.Count).End(xlToLeft).Column

    For c = 3 To n

        Set wbkT = Workbooks.Add(xlWBATWorksheet)

        Set wshT = wbkT.Worksheets(1)

        wshS.Range("A:B").Copy Destination:=wshT.Range("A1")

        wshS.Columns(c).Copy Destination:=wshT.Range("C1")

        wbkT.SaveAs Filename:=strPath & "\Column" & c & ".xlsx", _

            FileFormat:=xlOpenXMLWorkbook

        wbkT.Close SaveChanges:=False

    Next c

    Application.ScreenUpdating = True

End Sub

You can modify the filename of course (currently Column3, Column4, …)

Answer 2

You are amazing. Thank you so much!

Answer 3

Change

        wbkT.SaveAs Filename:=strPath & "\Column" & c & ".xlsx", _

            FileFormat:=xlOpenXMLWorkbook

to

        wbkT.SaveAs Filename:=strPath & "" & wshS.Cells(2, c).Value & ".xlsx", _

            FileFormat:=xlOpenXMLWorkbook

Answer 4

This is great, thank you. If it is not too cheeky for me to ask. Could you advise how I would change the file names to whatever text is showing in cell C2 once the spreadsheet has been split (ie text is originally from row 2 of column 3, row 2 of column 4, row 2 of column 5 etc).

Answer 5

Hi CW#,

Thanks for posting in the community, there is a solution using Power Query in Excel, you can follow the steps below to have a try:

1.    For example, there is a workbook “original.xlsx” and I want to split Column1, Column2 and each Advisor to multiple new workbooks.

 

2.    Then select File > New > create a new workbook “Book1”> Data > Get Data > From File > From Workbook

 

3.    Import “original.xlsx” > select the corresponding sheets > Transform Data

 

4.    Open Power Query Editor > Choose Columns > Select the columns you want to keep > Close & Load

 

5.    Then click File > Save as > save the workbook as “Advisor1.xlsx”

6.    Go back to “Book1” > Query > Edit and repeat the above steps to split the data

 

Note: The new workbooks “Advisor1”, “Advisor2”, “Advisor3” can access the original data through Query > Edit, if you don’t want users have access to the original data, you can click Delete to remove the Query data but keep the sheet data.

 

Since this method is operated manually, it could take some time. In you case, if you have too many advisors (columns) and you think it could be inconvenient, you can try to use the macro mentioned by @Hans.

Reference:Create a macro

If you have any concerns please feel free to come back, we are always willing to help you.

Best Regards,

Arck