470,638 Members | 1,582 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,638 developers. It's quick & easy.

Excel -Copy variable range from one sheet to another & copy formulas & then total

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.

Feb 29 '08 #1
0 3005

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Steven | last post: by
2 posts views Thread by Powerguy | last post: by
1 post views Thread by Randall Arnold | last post: by
1 post views Thread by Stoney L | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.