Windows XP, MS Office Excel 2003
If the tasks, I’m trying accomplish have been addressed previously (separately or in combination). Please, provide the links or keyword search to find them. I’ve been searching for code for each part of the task separately and trying to piece together multiple macros, that do something similar, to what I’m trying to accomplish in my over all task, but I’m not having a lot of luck. So, here’s the entire task, I’m trying to do, with two macros.
I was provided a spreadsheet with a macro that loads data from a SQL data base (not mine), it returns anywhere from one row to 100’s of rows. I copied this sheet to my destination workbook. The Data loads in 7 columns starting with B4:H4, with the column headings titles: Acct#, ID#, Name, Qtr1 Totals, Qtr2 Totals, Qtr3 Totals, and Qtr4 Totals.
First Macro:
I want the macro to copy the data for columns ID# thru Qtr4 Totals (C5:H5 down thru the last row with data) to another variable range on another sheet. The destination sheet range starts with rows B13:G13, with formulas to the right in columns H13, and J13:N13. H13 and N13 total the values across the row. (H-Totals Qtrs1-4 columns D:G and N totals amount from columns J thru M for each row) - column I is blank -
After or as the data is copied from the source sheet to the destination sheet, I need the macro to:
Copy the formulas in H13, and J13:N13 down for each row of data copied
Then skip a row and create a heading in Column C “Qtrly Totals as Reported:” and subtotals for columns D13:H13 and J13:N13 down thru the last row with data.
Then skip another row and put a row heading in Column C (Adjustments:).
- There could be no manual adjustments or 100’s -
Second Macro (separate macro/command button)
Then I need a macro to skip a row after the last row of manual adjustments
(There may be columns in each row with no entries, but a value will be in columns B & C (ID#, Name) and in at least one column D thru G in each row
Then create a heading in columns C "Qtrly Totals per Audit" and create final totals from the subtotals in the previous macro (Reported) and the manual adjustments in columns D thru H, and J thru N.
I hope this makes sense. I know Excel, but only a little VBA, and trying to piece together separate macros, that do something similar, but not exactly the same thing, for each step, has me pulling my hair out. Thanks in advance for any help you can give me on these macros.
Jim
Wyoming