K
KnightRiderAW
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.
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.