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]

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

I was wondering about this too...seems like the original code posted by Jindon it would copy two sheets, not three as you specified...

Maybe more typical would be:

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
        [COLOR="Blue"]If n = 3 Then[/COLOR]
            t = t + 1
            Sheets(Split("summary" & temp, "|")).Copy
            ActiveWorkbook.SaveAs Replace(ThisWorkbook.FullName, ".xls", ws.Name & ".xls")
            ActiveWorkbook.Close False
            [COLOR="#0000ff"]n = 0[/COLOR]: temp = ""
        End If
    End If
Next

alex

 

[seagreen]

Thanks Alex. I probably wasn't too clear as to whether I wanted three sheets including the summary sheet or three sheets in addition to the summary sheet. When I decided I was going to add another "dummy" worksheets after every 3 worksheets so that I would have a means to add the person's name to every filename, I ended up copying 4 worksheets so i use "-2" rather than "-1" in place of the n=0 that Jindon had.

Jindon's code allowed me to tweak to add new features I hadn't originally asked for. I will look at your too, Alex, so I can learn another way to tackle it.

Now I'm working on another problem (I posted another thread today) where I need to copy worksheet ranges from individual workbooks into the master. Another facet of my overall project. My overarching goal is to read workbooks from a directory, copy three sheets from each into a master, do some massaging in the master workbook, split the master into the individuals again, and mail them automatically using a personalized greeting, as well as mail the master to another person.

I've got most of the massaging in the master workbook done, I think I'm close to getting the mailing part solved, so it's mostly the copying from the individuals that I have yet to finish.

Thanks again!

 

[xenou]

ah - so I was wondering about that too - normally your counters start at 0 but if using negative two works, "don't mess with success"...

 

[jindon]

Change to

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

 

[seagreen]

Thanks Jindon! Because of the number and order of my worksheets within the master, I used -2 to get it to work. I have the famous "summary" sheet first, a sheet to be skipped over 2nd, then the four I want in the first split off workbook, the next four, the next four after that, etc... until the end where I have three sheets that I don't want for a split file. If I use the -2 it gives me the right splits with a file at the beginning and one at the end that have the sheets I only need in the master. So my change isn't elegant but it's working. I am looking forward to the day when I understand the code well enough to make a more elegant solution. Again, I am SO grateful for your help!