Group Sheets By Year and Output to New Files

[ClaireLaf]

Hello - I have a workbook containing data from several years. There are three tabs for each year. I would like to output the tabs for each year to their own workbook. I have already used another macro to sort the tabs into ascending order by year, but I cannot seem to figure out how to loop through, find groups of 3 tabs for each year, and output each group to a file. Can anyone assist me with this task?


To summarize, in then end, I would like to have multiple workbooks, each containing all three tabs for that particular year. The workbooks can just be named after the year they contain.


Thank you in advance!

 

[Yongle]

What are the sheets names?
- please provide the names of 3 sheets for one year
- are the other years named in the same style?

 

[ClaireLaf]

The sheet names are similar to 2014 - Reports, 2014 - Inputs, 2014 - Exports, and so on for each year. Each year uses the same format and tab names. The tab always starts with the year as four digits.

 

[WaterGypsy]

If you start with something like this.
Obviously you will need to change the names I have used ..... it loops through all your sheets looking for whatever identifies them as being in that year (you might populate them outside this macro and call it for each year) ... copying the sheets puts them in a new workbook ready to save ... again you probably want to populate the file name outside this macro

Sub Looping()
Dim sh As Variant
Dim sh1 As Variant
Dim sh2 As Variant
Dim sh3 As Variant





For Each Sheet In ActiveWorkbook.Sheets
    If sh.Name = strTab1 Then
        sh1 = sh
    Else
        If sh.Name = strTab2 Then
            sh2 = sh
        Else
            If sh.Name = strTab3 Then
                sh3 = sh
            End If
        End If
    End If
Next sh

Worksheets(Array(sh1.Name, sh2.Name, sh3.Name)).Copy
ActiveWorkbook.SaveAs (sOutputfilename)
ActiveWorkbook.Close
    
End Sub

 

[Fluff]

Another option

Sub CopyShts()
   Dim i As Long
   For i = [COLOR=#ff0000]1[/COLOR] To [COLOR=#ff0000]12 [/COLOR]Step 3
      Sheets(Array(Sheets(i).Name, Sheets(i + 1).Name, Sheets(i + 2).Name)).Copy
      ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Left(Sheets(1).Name, 4) & ".xlsx", 51
      ActiveWorkbook.Close
   Next i
End Sub

Change the numbers in red to reflect the first & last sheets you need

 

[ClaireLaf]

Thanks WaterGypsy and Fluff! I will try both of these to see which works better for me. Thank you both for your assistance!!