Split workbook into files

[DicksonKwok]

Hi all,

I wish to split the workbook (contain some input spreadsheet and a guideline page named as "Guideline")

I got the VBA code to split all the spreadsheets into files, but I am curious whether there is a code can save split the workbook with the targeted input spreadsheet name PLUS a guideline page?

Look like:

5 Spreadsheets of the Master File:
Consolidated Page
Finance
HR
CEO
Supporting

I want the output to be 3 separate file:
File name: Finance
Cover spreadsheet(s): Finance, Supporting
File name: HR
Cover spreadsheet(s): HR, Supporting
File name: CEO
Cover spreadsheet(s): CEO, Supporting

Thanks in advance.

 

[HaHoBe]

Hi DicksonKwok,

what about

Sub MrE1221063_161330D()
'https://www.mrexcel.com/board/threads/split-workbook-into-files.1221063/

Dim varSheets       As Variant      'array of sheets to copy
Dim varReports      As Variant      'array of new sheets to create
Dim lngCounter      As Long         'for looping through the

varReports = Array("Finance", "HR", "CEO")

For lngCounter = LBound(varReports) To UBound(varReports)
  Select Case varReports(lngCounter)
    Case "Finance"
      varSheets = Array("Guideline", "Finance", "Supporting")
    Case "HR"
      varSheets = Array("Guideline", "HR", "Supporting")
    Case "Finance"
      varSheets = Array("Guideline", "CEO", "Supporting")
    Case Else
  End Select
  If IsArray(varSheets) Then
    Sheets(varSheets).Copy
    ActiveWorkbook.SaveAs Application.DefaultFilePath & "\" & varReports(lngCounter) & Format(Now, "_yymmdd_hhmmss") & _
          ".xlsx", FileFormat:=51
    ActiveWorkbook.Close True
    Erase varSheets
  End If
Next lngCounter

End Sub

Code will create new workbooks with a Date/Time stamp added to the filename as macrofree workbooks in the folder which is set as standard.

Ciao,
Holger

 

[DicksonKwok]

it works! thanks Hahobe!