Split Excel Worksheet to diffrent workbook

[Chandresh]

HI All,

I have a macro that splits the excel worksheets into different workbook, just wanted to check is there any way that we can create the separate workbook based on sheet name mentioned in master work sheet.
example I have a workbook with more than 30 sheets and the first sheet is the master sheet were I have mentioned the name of all sheet in column A and the workbook to be made in column B.

if in column A - (sheet 1, Sheet 2, Sheet 3) , 3 sheets are mentioned against one workbook(column B- "Chan") then the macro should create a workbook with 3 sheets(sheet 1, Sheet 2, Sheet 3) naming as Chan.

Thanks

 

[Legacy 456129]

You really don't need that list since all the worksheets are part of a collection. Roughly the code goes like

Dim wbkOrigin As Workbook,  wbkDestin As Workbook
Set wbkOrigin = ThisWorkbook 'to set an alias pointer to your source workbook
For Each Worksheet In wbkOrigin.Worksheets
   If Worksheet.Name is <> "Master" Then Workbook.Add 'this opens a new workbook with Sheet1 by default
   Set wbkDestin = ActiveWorkbook 'to create a unique reference to the new workbook
   copy the sheet from wbkOrigin.Worksheets(Worksheet.Name) to wbkDestin 'Your new workbook alias
   Application.DisplayAlert = False 'to prevent a prompt
   wbkDestin.SaveAs Filename:=wbkOrigin.Path & "\" & Worksheet.Name, FileFormat:=50, CreateBackup:=False
   wbkDestin.Close savechanges:=false
   Application.DisplayAlert = True 'to reanable prompts
Next worksheets

The copying of worksheets creates all sorts of legacy datalinks and range names to go to your targeted workbooks too. Doing similar, purely excporting tables and ranges off sheets and than running a routine to remove all shapes, comments, rangenames and breaklinks.

Just something to get you on the way.