Macro to seprate each sheet as workbook with a Master sheet in all the new workbook

Dear All,

I have a workbook with several sheets in Excel 2010

One of the sheet name is Master which is the 4 sheet in the workbook

After Master I have many sheets which I want to export as separate workbooks.

I found this macro which exports each sheet as workbook

Sub Splitbook()
'Updateby20140612
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

My requirement is to have each workbook together with the Master sheet

Any sheet before the Master sheet need not be exported.

The newly exported workbook will have two sheets i.e. Master and each respective sheet

 

Hope to get some help with the above code or a new code

 

Thanks

 

Rashid Khan

 

Top Contributor Asked on June 18, 2017 in VBA: Macros.
Add Comment
1 Answer(s)

Hi,

Here is the VBA code to Split the Workbook into different Files by Each Sheet in the file  and save it in the required Folder.

Sub SplitWorkBook_BasedOnCondition()
'Updateby20140612
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
blnExport = False
For iCntr = 1 To ThisWorkbook.Sheets.Count
Set xWs = Sheets(iCntr)
If blnExport = True Then
Sheets(Array("Master", xWs.Name)).Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Else
If xWs.Name = "Master" Then blnExport = True
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Keymaster Answered 5 days ago.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.