CallSignPilot
New Member
- Joined
- Aug 4, 2022
- Messages
- 1
- Office Version
- 2016
- Platform
- Windows
How can I save worksheets based on values in two columns?
I have a "Formulas" worksheet (consisting of various Excel code I use to pull data needed from "Data").
I have a "Charges" worksheet (consisting of A:O, this is essentially individual account charges)
I have a "List" worksheet (A is account numbers, B consists of the company that account in the same row A belongs to)
I loop through A2:A in "List" to generate worksheets.
How can I save all worksheets into workbooks based on the company they belong too?
E.g., I would like all account worksheets that belong to Company 1 (listed on "List") saved to a workbook called Company 1 (I don't want Formulas or the Charges worksheet to be saved).
Related, before I save all these worksheets, how can I remove formulas in range A1:C on each worksheet (not Formula/Data worksheets) so that I don't need to waste so much space/RAM?
Finally, is there a way to create worksheets based accounts for one company in A of "List", save these worksheets to a new workbook based on the company name, and then delete the new sheets, before repeating this for the next grouping of accounts?
I fear that I have more accounts per company than Excel will permit worksheets (estimate around 500).
Many thanks.
I have a "Formulas" worksheet (consisting of various Excel code I use to pull data needed from "Data").
I have a "Charges" worksheet (consisting of A:O, this is essentially individual account charges)
I have a "List" worksheet (A is account numbers, B consists of the company that account in the same row A belongs to)
I loop through A2:A in "List" to generate worksheets.
VBA Code:
Sub Accounts_Companies()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
With Sheets("List")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
Sheets("Formulas").Copy Before:=Sheets("Formulas")
ActiveSheet.Name = .Range("A" & i).Value
Next i
End With
Application.ScreenUpdating = True
End Sub
How can I save all worksheets into workbooks based on the company they belong too?
E.g., I would like all account worksheets that belong to Company 1 (listed on "List") saved to a workbook called Company 1 (I don't want Formulas or the Charges worksheet to be saved).
Related, before I save all these worksheets, how can I remove formulas in range A1:C on each worksheet (not Formula/Data worksheets) so that I don't need to waste so much space/RAM?
Finally, is there a way to create worksheets based accounts for one company in A of "List", save these worksheets to a new workbook based on the company name, and then delete the new sheets, before repeating this for the next grouping of accounts?
I fear that I have more accounts per company than Excel will permit worksheets (estimate around 500).
Many thanks.