Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Macro for pulling information into one sheet
I need to create an Excel 2003 macro that can automatically update information from many different other sheets into one new sheet. I have a number of sheets with the following setup: A B C D E F-K L Date 5-digit code Comment $ Amt ea. Quantity Comments Total Now, some of the tricky parts are that the date (column A) may be mm/dd/yyyy or simply mm/yy and it is not on every row and the 5-digit code is at the top of each section in a red color (only time it is used in column B) where the rest of column B is text or comments. Also, rows may be inserted into each different section with new dates, amounts, etc. at any time by many different users. We just need a way to quickly update all of these amounts accurately into a sheet as below. What I want is to take the total (Column L) of every item with a Date (Column A) and put it in a new sheet based on 5-digit Code (Column B), so that it looks something like this: A B C D E (through N) Row 1 5-digit Code 2 Total Jan. Feb. Mar. April etc. (for ea. month) 3 $(Sum of months) $ $ $ $ $ Example sheets below: Original Sheet A B C D E L Row 1 63400 Travel Row 2 Month Survey Airfare Row 3 Jan-09 Delta Comment $860 1 =D3*E3 Row 4 2/17/09 Delta Comment $775 2 =D4*E4 Row 5 Row 6 61600 Meals Row 7 Month Site Meals Row 8 1/28/09 Expense Rep. Comment $150 1 =D8*E8 Row 9 Feb-09 Expense Rep. Comment $250 1 =D9*E9 (Macro should pull everything into Row below months) New Sheet (prior to Macro running): A B C D E (etc.) Row 1 61600 Meals Row 2 Total Amount January February March Row 3 Per Mth. =SUM(C3:N3) Row 4 Percent. 100% =C3/B3 =D3/B3 =E3/B3 Row 5 Row 6 63400 Travel Row 7 Total Amount January February March Row 8 Per Mth. =SUM(C8:N8) Row 9 Percent. 100% =C8/B8 =D8/B8 =E8/B8 New Sheet (after running Macro, which sorts other sheets and auto places numbers into correct cell below each month for each code): A B C D E (etc.) Row 1 61600 Meals Row 2 Total Amount January February March Row 3 Per Mth. $400 $150 $250 Row 4 Percent. 100% 38% 62% #DIV/0! Row 5 Row 6 63400 Travel Row 7 Total Amount January February March Row 8 Per Mth. $2410 $860 $1550 Row 9 Percent. 100% 36% 64% #DIV/0! Hopefully this makes sense. Be aware that we do utilize an entire year (but never cross years, i.e. having an amount from March of 2008 and March of 2009). Also, we have about 20 different 5-digit codes spread across multiple sheets (i.e. one sheet may only have one code, where another sheet may have 10 codes) that all have to come to this one final sheet. Thanks for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling information from master sheet to multiple sheets | New Users to Excel | |||
Pulling Information into a sheet via date | Excel Discussion (Misc queries) | |||
Pulling specific information from a sheet | Excel Programming | |||
Pulling cell information from one sheet to another in same databas | Excel Discussion (Misc queries) | |||
Pulling all information right of this "(" | Excel Discussion (Misc queries) |