Group Sheets By Year and Output to New Files

ClaireLaf

New Member
Joined
Oct 23, 2018
Messages
3
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!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
What are the sheets names?
- please provide the names of 3 sheets for one year
- are the other years named in the same style?
 
Upvote 0
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.
 
Upvote 0
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

Code:
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
 
Upvote 0
Another option
Code:
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
 
Upvote 0
Thanks WaterGypsy and Fluff! I will try both of these to see which works better for me. Thank you both for your assistance!!
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top