Export Grouped Worksheets Based on Data Set

HookFreckle

New Member
Joined
Apr 17, 2024
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi! I have a file with 50+ worksheets for invoicing different groups. Each invoice has a header worksheet and one or more detail worksheets. I need to be able to loop through the active sheets and export each Group of worksheets into their own individual Excel File.

Ex: I need one file called ADMIN-0424.xlsx that contains both the Admin 23 and Admin Detail 23 worksheets, then a second file called SKILLSET-0424 that contains the 4 sheets in Group 2, etc.

GroupWorksheet NameCategoryFile Name
1​
Admin 23ProjectsADMIN-0424
1​
Admin Detail 23ProjectsADMIN-0424
2​
SkillsetStaff AugSKILLSET-0424
2​
Skillset Detail - JinxStaff AugSKILLSET-0424
2​
Skillset Admin DetailsStaff AugSKILLSET-0424
2​
Skillset R JonesStaff AugSKILLSET-0424
3​
Associate SpecialistStaff AugASSOCIATE-0424
3​
Associate Specialist DetailsStaff AugASSOCIATE-0424
4​
Info SpecialistStaff AugINFO-0424
4​
Info Specialist DetailsStaff AugINFO-0424

This is what I have thus far that successfully exports the active sheets with an invoice name in cell I8, but doesn't loop through. I have the above table saved on a sheet called "Groupings". I know there's gonna be a loop involved, this is just a bit beyond my skillset currently.

'For loop exports each visible/active sheet as its own excel named as the value in Cell I8
For Each sh In ActiveWorkbook.Worksheets

'This makes sure it's an active/visible sheet
If sh.Visible Then

'This includes only sheets where an Invoice Total > $0
If (WorksheetFunction.SumIfs(sh.Range("I10:I100"), sh.Range("H10:H100"), "Total") > 0) Then

On Error Resume Next

sh.Copy

ActiveWorkbook.SaveAs Filename:=Folder_Path & Application.PathSeparator & sh.Range("I8") & ".xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

ActiveWindow.Close

End If

End If

Next

Thank you so much in advance!!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of one or two of the your invoicing sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

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