• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split a workbook by making first two sheets as a separate workbook and then take the 3rd and 4th sheet and make it a separate workbook

Amir Wisal

New Member
Hi All,

I am new to VBA and need to split a workbook in such a way that it take the first two sheets and make it a new workbook, save it to a desired folder and also save a PDF version of the new workbook created (obviously by taking the first two sheets).

My workbook has variable number of sheets but it will always comes in pairs (2 each). I want to have a dynamic vba code which will loop through the workbook and split the first two sheets, save it in excel as well PDF and then go the third and fourth sheet, save it in excel and PDF as well and so on.

I have recorded a macro for it but as you'll be aware that Macro it static and i want it to be dynamic - meaning to loop through the whole workbook. Further, it would be great if the saved excel and PDF will rename itself based on the first sheet of each pair. Hope i am clear with the instructions but if further details are required, please do let me know.

The macro that i have recorded is saved in the attached excel spreadsheet.

Thank you All in advance.
 

Attachments

  • Merged.xlsm
    71.7 KB · Views: 8
Hi Marc L,

Thank you for responding.

Attached are the desired result of the first four sheets. please have a look.

Desired results is to save the split workbook (first two pages) as a separate excel workbook as well as PDF. the macro needs to be dynamic to loop through the workbook and combine each two sheets close to one another and split it into new workbook and save them.

Many thanks,
 

Attachments

  • Sheet 1.pdf
    41.6 KB · Views: 1
  • Sheet 1.xlsx
    16.1 KB · Views: 3
  • Sheet 1 (2).pdf
    37.1 KB · Views: 2
  • Sheet 1 (2).xlsx
    18.4 KB · Views: 2
According to your static attachments a VBA demonstration for starters :​
Code:
Sub Demo1()
        Dim N&
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    With ThisWorkbook.Worksheets
        For N = 2 To .Count Step 2
           .Item(Array(N - 1, N)).Copy
            ActiveWorkbook.ExportAsFixedFormat 0, .Parent.Path & "\" & .Item(N - 1).Name
            ActiveWorkbook.SaveAs .Parent.Path & "\" & .Item(N - 1).Name, 51
            ActiveWorkbook.Close
        Next
    End With
        .DisplayAlerts = True
        .ScreenUpdating = True
        .Speech.Speak "Done!", True
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Hi Marc,

Hope you're doing great.

Modification Required

The above code works perfectly fine, but when I change the sheet names, it splits the worksheets but does not show them as excel files. the pdf part is fine. i think there is an issue if I use period character "." in the worksheet name.

I have updated the names as follows;
sheet 1 to 1. Co. 100 to Co. 27-P6
Sheet 1 (2) to 2. Co. 100 to Co. 76-P6

Thanks,
 
Back
Top