HookFreckle
New Member
- Joined
- Apr 17, 2024
- Messages
- 1
- Office Version
- 365
- Platform
- 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.
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!!
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.
Group | Worksheet Name | Category | File Name |
1 | Admin 23 | Projects | ADMIN-0424 |
1 | Admin Detail 23 | Projects | ADMIN-0424 |
2 | Skillset | Staff Aug | SKILLSET-0424 |
2 | Skillset Detail - Jinx | Staff Aug | SKILLSET-0424 |
2 | Skillset Admin Details | Staff Aug | SKILLSET-0424 |
2 | Skillset R Jones | Staff Aug | SKILLSET-0424 |
3 | Associate Specialist | Staff Aug | ASSOCIATE-0424 |
3 | Associate Specialist Details | Staff Aug | ASSOCIATE-0424 |
4 | Info Specialist | Staff Aug | INFO-0424 |
4 | Info Specialist Details | Staff Aug | INFO-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!!