To split worksheets in a single file to multiple files

[Ecel Dumbo]

Hi Dear Friends,

I have attached a spreadsheet file, which has multiple sheets named-Harry, Steve, Thomas etc

I will need a technique where I can split this single file in to multiple files based on the worksheets within the original single master file. So once I have the split, I would like to the files to be named based on the names in the master file

example- Harry.xlsx, Steve.xlsx, Thomas.xlsx etc

This is just a sample file, I will have a huge number of worksheets (16 nos) in the master file and I occasionally do add and delete worksheets from the master file

Any help will be greatly appreciated
Thanks
Dumbo

 

[Hui]

Have a look at these two posts :

Move Data to other Sheets
http://chandoo.org/wp/2012/05/14/vba-move-data-from-one-sheet-to-multiple-sheets/
Advanced Filter Move Data to other Files
http://chandoo.org/wp/2011/10/19/split-excel-file-into-many/

 

[Smallman]

Hi ED

While this is a frequently asked question the code to achieve this task can be a long way from succinct. This should get you over the line.

Option Explicit
 
Sub SplitMe()
Dim ar As Variant
Dim i As Integer
 
ar = Sheet1.Range("A4", Range("A" & Rows.Count).End(xlUp))
 
    For i = 1 To UBound(ar)
        Sheets(ar(i, 1)).Copy
        ActiveWorkbook.SaveAs "D:\Test\" & ActiveSheet.Name & ".xlsx"
        ActiveWorkbook.Close 0
    Next i
End Sub

Change the path to suit. Will attach a file to show workings.

Take care

Smallman