Hello all, I am trying to create a worksheet to track numerous financial accounts. I have a "Data" tab that receives input from another VBA ("GL" sheet) that adds new rows of data (no overwriting) and then refreshes pivots. The data it receives is "Pulse Account", "Comments", "Amount", "Date". Within the data tab I have columns that give "Month", "Year", "Debit", "Credit", and "GL Account"; all of these are formula based. In specific, "GL Account" uses vlookup to cross reference the "Pulse Account" to the corresponding GL Account on another tab. What I would like to do is as follows:
Create a VBA based button that does the following functions:
1. Identifies unique "GL Account" values from the "Data" tab.
2. Creates new worksheets named after the unique value from above.
3. Ensures there are no duplicate sheets.
4. Refreshes all Pivots on all sheets.
From there I would make a pivot table on each sheet using the "Data" tab as a dynamic range, filtering to just the "GL Account" the sheet is named after. It would contain a "Year" filter, with columns for "Month" and rows for SUM of "Debit", SUM of "Credit", and Totals. I don't think there is way to automate this pivot creation.
My goal is to have a workbook where the 4 original data elements can be entered, the data tab generate the rest of the information, and then the VBA code ensure there are worksheets for each "GL Account". Data added for GL Accounts that already have a sheet would simply update the pivot. I hope this make sense. I have been learning VBA and finally got my add data button to work
Create a VBA based button that does the following functions:
1. Identifies unique "GL Account" values from the "Data" tab.
2. Creates new worksheets named after the unique value from above.
3. Ensures there are no duplicate sheets.
4. Refreshes all Pivots on all sheets.
From there I would make a pivot table on each sheet using the "Data" tab as a dynamic range, filtering to just the "GL Account" the sheet is named after. It would contain a "Year" filter, with columns for "Month" and rows for SUM of "Debit", SUM of "Credit", and Totals. I don't think there is way to automate this pivot creation.
My goal is to have a workbook where the 4 original data elements can be entered, the data tab generate the rest of the information, and then the VBA code ensure there are worksheets for each "GL Account". Data added for GL Accounts that already have a sheet would simply update the pivot. I hope this make sense. I have been learning VBA and finally got my add data button to work