• Ask a Question
150
Insert Image Size must be less than < 5MB.
    Ask a Question
    Cancel
    150
    More answer You can create 5 answer(s).
      Ask a Poll
      Cancel
      Top Contributor

      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

       

      Asked by prkhan56 on June 18, 2017 in VBA: Macros.
      1 Answers
      Keymaster

      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
      
      Answered by PNRao on August 6, 2017..