How to split every 3 worksheets into their own new wkbk

[seagreen]

Hi, this is my first post here. I've searched through the forums and cannot find a previous post that answers my question. I'm just starting to learn how to code in Excel VBA. Can anyone help with how I can write a subroutine that will take a batch of every three worksheets and write them out to a new workbook? I have a master workbook with a group of three worksheets for each person (60 worksheets in all plus a couple of summary sheets) and would like to split each person's three worksheets plus a copy of the one universal summary sheet out to a new workbook every week.

I've seen code that creates a new workbook for every worksheet but not every three. I tried writing a For each...Next loop with a counter that skips to every third (at least to get me started) but I do not know the syntax well enough to even do that.

I'll continue reading my VBA books and if I get an answer I'll come back and post it here but if anyone could help in the meantime I'd be very grateful.
Thanks.

Seagreen

 

[xenou]

Hi,

Welcome.
What have you tried so far?

It seems you could just use a worksheet.copy type of command...

Pseudocode (not tested):

Dim wbSource as Workbook
Dim wbDestination as Workbook
Dim i as Long
Dim j as Long

[COLOR="seagreen"]'//Original workbook[/COLOR]
Set wbSource = ThisWorkbook

i = 2[COLOR="SeaGreen"] '//Assuming Summary is first sheet[/COLOR]
Do While i < 60  [COLOR="seagreen"]'//Or "Do while i < wbSource.Worksheets.Count"[/COLOR]

   [COLOR="seagreen"] '//New workbook[/COLOR]
    Set wbDestination = Workbooks.Add

    [COLOR="seagreen"]'//Copy summary sheet[/COLOR]
    wbSource.Sheets("Summary").Copy Destination:=wbDestination

    [COLOR="seagreen"]'//Copy next three sheets[/COLOR]
    For j = 0 to 2
        i = i + j
        wbSource.Sheets(i).Copy Destination:=wbDestination
    Next j
    i = i + 1 [COLOR="SeaGreen"]'//Increment so we start on next worksheet next time....[/COLOR]

Loop

 

[seagreen]

Thanks Alexander I will try your logic. I am reading Excel VBA Macros for Excel 2007 (or a title close to that) so I can figure out how to write the syntax. I'll post again when I have something working (or at least partially working). Thanks again!

 

[xenou]

No problem...you came to the right place. There's a number of us who are always fiddling with Excel vba. I can't tell you how many times I spend 20 minutes writing a macro to do a job that would take 25 minutes by hand and 10 seconds in code - saving myself 4 minutes and 50 seconds to rest on my laurels while I enjoy a cup of coffee!

Alex.

 

[jindon]

Something like this ?

Sub test()
Dim ws As Worksheet, n As Long, t As Long
For Each ws In ThisWorkbook.Sheets
    If ws.Name <> "summary" Then
        n = n + 1
        temp = temp & "|" & ws.Name
        If n = 2 Then
            t = t + 1
            Sheets(Split("summary" & temp, "|")).Copy
            ActiveWorkbook.SaveAs Replace(ThisWorkbook.FullName, ".xls", t & ".xls")
            n = 0
        End If
    End If
Next
End Sub

 

[seagreen]

Thanks jindon! I tried it and it writes a new workbook with the name of my workbook witha "1" concatenated on the name but then I get a "subscript oout of range" error at this line:
Sheets(Split("Summary" & temp, "|")).Copy
The newly created workbook is still open and has the sheet "summary" and the next worksheet in it for a total of two sheets.

I am using Excel 2007 at home here in case that's what's wrong...

Thanks again.

 

[jindon]

try this

test()
Dim ws As Worksheet, n As Long, t As Long
For Each ws In ThisWorkbook.Sheets
    If ws.Name <> "summary" Then
        n = n + 1
        temp = temp & "|" & ws.Name
        If n = 2 Then
            t = t + 1
            Sheets(Split("summary" & temp, "|")).Copy
            ActiveWorkbook.SaveAs Replace(ThisWorkbook.FullName, ".xls", t & ".xls")
            ActiveWorkbook.Close False
            n = 0 : temp = ""
        End If
    End If
Next
End Sub

 

[seagreen]

Thanks Jindon. Very, very nice! The code works great. I really appreciate it. BTW, is setting a variable to "" the same as setting it to "Nothing"?

 

[jindon]

As temp is a String type variable, it need to be initialized such like

temp = "" or temp = vbNullString to clear it.

Nothing can be used for Object type and Empty for Variant type variable.

and of-course 0 for numeric type variable.

 

[seagreen]

Thanks Jindon. Now I see, it varies with the variable type.

I worked with the code you gave me and did a few small tweaks to vary the number of sheets that are copied to each new workbook and added a "dummy sheet" at the end of each group of sheets that I want to copy so that I can use the name of the tab as what is appended to the master workbook name:

Sub test()
Dim ws As Worksheet, n As Long, t As Long
For Each ws In ThisWorkbook.Sheets
    If ws.Name <> "summary" Then
        n = n + 1
        temp = temp & "|" & ws.Name
        If n = 2 Then
            t = t + 1
            Sheets(Split("summary" & temp, "|")).Copy
            ActiveWorkbook.SaveAs Replace(ThisWorkbook.FullName, ".xls", ws.Name & ".xls")
            ActiveWorkbook.Close False
            n = -2: temp = ""
        End If
    End If
Next
End Sub

The changes I made: Changed n= 0: temp = "" to
n = -2: temp = ""

And changed:

  ActiveWorkbook.SaveAs Replace(ThisWorkbook.FullName, ".xls", t & ".xls")
to:
  ActiveWorkbook.SaveAs Replace(ThisWorkbook.FullName, ".xls", ws.Name & ".xls")

So that the name of one of the worksheets is appended to the master spreadsheet name rather than the counter. I guess I could take the t variable out of the code altogether. I will be disbursing the split workbooks to people so I wanted to add their names to the file name.

I think adding a dummy worksheet with the tab named so that it would append to the master workbook name is a kludge as I have a worksheet in there without any other purpose but that'll have to do until I get better at coding. I can always put content in the sheet so that it's not obvious that I have a "useless" sheet in there.

Thanks again for the help everyone who answered!