Excel VBA Tab-Splitting Macro
Budget: $30 – $250 USD
I have a single Excel workbook that I drop in data each year that's an excel exported general ledger from QBO. I want a VBA macro that will do all the heavy lifting at the click of a button:
• First, it must read the "organization" tab to obtain the names needed to pull data from the master sheet, columns A-G, and put into the newly created tab and to name the tab, break the rows out by Organization name, and create (or refresh) one tab per organization from the list on the "organization" tab. while doing this, all cells in the date column that are blank need to be ignored and all "date" cells that say "Beginning Balance" to be removed from the copied data to the tab and then summed up and placed in L2 which would fall just above and one cell to the right of the pivot table end. and in k2 it would say "Beginning Balance".
• Inside every new tab I need 5 functions with 4 sets of data:
1. A pivot table built from the newly filtered data that starts placement in J3 should pull "rows" from column G and values from column f.
2. A report of data on each tab, starting in cell with a list of transactions compiled on the new tab, using column e for the vendor or donor name and column f for the amount and G that are coded to donations or contributions that will show a total of the transactions with the name of each one. then show those that are donations that => 5k or more from one entity or person and show those that are contributions that =<(-5k) or less from one entity or person. this report should start on column N showing the names going down, then the positive totals showing next in column o, then the negative totals showing in column p. the next two columns, q and r should show only the positive amounts totaling over 5k and the negative amounts totaling -5k or less.
3. Using the above data from 3, specifically column n starting the report on t3 titled "Name" then column u3 for the address, titled "Address", that can be looked up using the name from column t using the "Vendor and Donor List Combined" tab column a for the name and b for the address, and column q to complete column U3 which should be titled "Amount", a "Schedule B" (should be displayed in cell U2) section for donations the organization received from other organizations or persons over 5k showing their name, the address which would be found in the "Vendor & Donor List Combined" and the total amount from said individual or organization, 3 columns, would be created and the same for "Schedule I" starting in cell x3 through z3, for contributions that received (-5k) or less (technically this is represented as a negative number in the transaction data but on the schedule I it should be converted to appear as a positive number and would contain the same data as schedule b Two print-ready ranges that the macro then exports as separate, clearly named PDFs and stores in the same folder as the workbook and in landscape format.
4. Finally "Schedule R" the exact format of how this needs to be displayed in each tab, starting in column ab2 of each organization tab is already contained in a tab in the workbook titled "Schedule R". the amount that needs to be completed on each tab within this report, once it is copied over, is the column titled "Amount (+/(-) In/(-Out))" referred to as amount column . The first name appears in column a row 4 of the original data so it should appear on AB5 of each organizations tab. That organization name needs to be looked up in the tab and all instances of that name that appears in column E the amount in column F should be added using a similar formula like the "sumif" formula. the following 2 rows need to be skipped and then the next name in the 3rd row from the first one needs to be looked up and so on until the end of that data has been reached, ending in row 427. The only name that needs to be removed from this list is the name for the organization for which the tab belongs.
Everything has to run natively in Excel; no external add-ins are allowed. Where possible, pleas keep the code parameter-driven so I can update source columns, output paths, or naming conventions from a simple control sheet rather than digging into the VBA each time.
Acceptance criteria
• Running the macro on a fresh copy of the raw file generates one tab per organization with all three components correctly populated.
• All three PDFs for every organization are automatically created without user prompts, named “OrgName_Report1.pdf”, “OrgName_Report2.pdf”, and “OrgName_Report3.pdf”, and saved in a dedicated sub-folder.
• The process is repeatable—running the macro again should delete or refresh existing sheets and PDFs cleanly rather than duplicating them.
If you have produced similar VBA automation before, a quick demo or code sample will help me choose faster.
• First, it must read the "organization" tab to obtain the names needed to pull data from the master sheet, columns A-G, and put into the newly created tab and to name the tab, break the rows out by Organization name, and create (or refresh) one tab per organization from the list on the "organization" tab. while doing this, all cells in the date column that are blank need to be ignored and all "date" cells that say "Beginning Balance" to be removed from the copied data to the tab and then summed up and placed in L2 which would fall just above and one cell to the right of the pivot table end. and in k2 it would say "Beginning Balance".
• Inside every new tab I need 5 functions with 4 sets of data:
1. A pivot table built from the newly filtered data that starts placement in J3 should pull "rows" from column G and values from column f.
2. A report of data on each tab, starting in cell with a list of transactions compiled on the new tab, using column e for the vendor or donor name and column f for the amount and G that are coded to donations or contributions that will show a total of the transactions with the name of each one. then show those that are donations that => 5k or more from one entity or person and show those that are contributions that =<(-5k) or less from one entity or person. this report should start on column N showing the names going down, then the positive totals showing next in column o, then the negative totals showing in column p. the next two columns, q and r should show only the positive amounts totaling over 5k and the negative amounts totaling -5k or less.
3. Using the above data from 3, specifically column n starting the report on t3 titled "Name" then column u3 for the address, titled "Address", that can be looked up using the name from column t using the "Vendor and Donor List Combined" tab column a for the name and b for the address, and column q to complete column U3 which should be titled "Amount", a "Schedule B" (should be displayed in cell U2) section for donations the organization received from other organizations or persons over 5k showing their name, the address which would be found in the "Vendor & Donor List Combined" and the total amount from said individual or organization, 3 columns, would be created and the same for "Schedule I" starting in cell x3 through z3, for contributions that received (-5k) or less (technically this is represented as a negative number in the transaction data but on the schedule I it should be converted to appear as a positive number and would contain the same data as schedule b Two print-ready ranges that the macro then exports as separate, clearly named PDFs and stores in the same folder as the workbook and in landscape format.
4. Finally "Schedule R" the exact format of how this needs to be displayed in each tab, starting in column ab2 of each organization tab is already contained in a tab in the workbook titled "Schedule R". the amount that needs to be completed on each tab within this report, once it is copied over, is the column titled "Amount (+/(-) In/(-Out))" referred to as amount column . The first name appears in column a row 4 of the original data so it should appear on AB5 of each organizations tab. That organization name needs to be looked up in the tab and all instances of that name that appears in column E the amount in column F should be added using a similar formula like the "sumif" formula. the following 2 rows need to be skipped and then the next name in the 3rd row from the first one needs to be looked up and so on until the end of that data has been reached, ending in row 427. The only name that needs to be removed from this list is the name for the organization for which the tab belongs.
Everything has to run natively in Excel; no external add-ins are allowed. Where possible, pleas keep the code parameter-driven so I can update source columns, output paths, or naming conventions from a simple control sheet rather than digging into the VBA each time.
Acceptance criteria
• Running the macro on a fresh copy of the raw file generates one tab per organization with all three components correctly populated.
• All three PDFs for every organization are automatically created without user prompts, named “OrgName_Report1.pdf”, “OrgName_Report2.pdf”, and “OrgName_Report3.pdf”, and saved in a dedicated sub-folder.
• The process is repeatable—running the macro again should delete or refresh existing sheets and PDFs cleanly rather than duplicating them.
If you have produced similar VBA automation before, a quick demo or code sample will help me choose faster.