Hi

I currently have a worksheet giving volumes sold per week (with a row for
each seller/product combination and each week as a column) and a second sheet
which calculates revenues via the first sheet and a lookup table, e.g.

'Revs'!C3 = 'Vol sold'!C3*vlookup($B3 [the product],prices,2,0)

As this formula is in every cell of the second sheet this is making my file
too large. I'm looking to replace it with a macro which would copy the
volumes into the revenue sheet and use Paste Special - Multiply to calculate
the revenues.

The only problem is that I need the macro to rerun whenever the workbook is
recalculated; the only events I have found on Excel help to run a macro run
it when a worksheet or chart is calculated, whereas I need this at the
workbook level. Is there an equivalent or can anyone suggest a way around
the problem?

Thanks

Keith

see this link

http://www.cpearson.com/excel/AppEvent.htm


will trap all worksheet calc events

--
When you lose your mind, you free your life.


"Keith" wrote:

Hi

I currently have a worksheet giving volumes sold per week (with a row for
each seller/product combination and each week as a column) and a second sheet
which calculates revenues via the first sheet and a lookup table, e.g.

'Revs'!C3 = 'Vol sold'!C3*vlookup($B3 [the product],prices,2,0)

As this formula is in every cell of the second sheet this is making my file
too large. I'm looking to replace it with a macro which would copy the
volumes into the revenue sheet and use Paste Special - Multiply to calculate
the revenues.

The only problem is that I need the macro to rerun whenever the workbook is
recalculated; the only events I have found on Excel help to run a macro run
it when a worksheet or chart is calculated, whereas I need this at the
workbook level. Is there an equivalent or can anyone suggest a way around
the problem?

Thanks

Keith