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!
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

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

Well-known Member
Joined
Jan 15, 2010
Messages
697
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,220
Office Version
  1. 365
Platform
  1. Windows
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
 

ClaireLaf

New Member
Joined
Oct 23, 2018
Messages
3
Thanks WaterGypsy and Fluff! I will try both of these to see which works better for me. Thank you both for your assistance!!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,176
Messages
5,600,140
Members
414,365
Latest member
UUR

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
Top