Can I split a spreadsheet into multiple files based on a column with keeping one sheet common in all files RRS feed

  • Question

  • Hi Dears,

    Appreciating your support to know, how I can split a spreadsheet into multiple files based on a column with keeping a common sheet with all splited files

    Wednesday, December 21, 2016 12:12 PM

All replies

  • Hi,

    What is the relationship between the column and the new files?

    Do you want to create several files according to the value in one column? For example, the files' names or sheets' names are written in this column. You want to create new files based on the values.

    To create news files and keep a common sheets based on a column, you could see the code below:

    Dim newWB As Workbook
    Dim newWS As Worksheet
    For Each cell In ActiveSheet.UsedRange.Columns("A").Cells
    Set newWB = Application.Workbooks.Add
    Set newWS = newWB.Sheets.Add
    newWS.Name = cell.Value

    To split sheets you could use code below:

    Path = Application.ActiveWorkbook.Path
    For Each ws In ThisWorkbook.Sheets
        Application.ActiveWorkbook.SaveAs Filename:=Path & "\" & ws.Name & ".xlsx"
        Application.ActiveWorkbook.Close False

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Thursday, December 22, 2016 3:04 AM