Excel VBA To Split Workbook Every 4 Worksheets

Question

I have a workbook that contains about 100 worksheets. I want to create 25 individual workbooks that contain 4 sheets each. I am trying to write an Excel VBA foor loop that will do such - but it is generating a workbook for every 1 page.

totalPages = PDDoc.GetNumPages

For i = 1 To totalPages - 4 
    'This is where you would add the workbook and transfer the page
Next i

You need to create a inner loop that move 4 sheets or create a single instruction to move 4 sheets. — user8753746
@DavidG. - I obviously do not have a firm a grasp on this concept as I thought I did then. — Smith Stanley
What is the instruction you are using that creates 1 workbook for each worksheet? — user8753746

Harassed Dad Harassed Dad · Accepted Answer · 2017-12-06T16:07:52

Sub SplitPages()
   Dim i As Integer

   Dim wb As Workbook

   For i = 1 To ThisWorkbook.Worksheets.Count
        If i Mod 4 = 1 Then
           Set wb = Workbooks.Add
        End If
       ThisWorkbook.Worksheets(i).Copy wb.Worksheets(1)
    Next i
End Sub

You have to copy to avoid messing up the count, and the new workbooks have an extra sheet - but a quick

  for each wb in workbooks:wb.sheets("sheet1").delete:next wb

will fix that

Excel VBA To Split Workbook Every 4 Worksheets

1 Answers