saving variables with a name on each sheet

  • Thread starter Thread starter keyser_Soze
  • Start date Start date
K

keyser_Soze

I would like to have a macro on the workbook page that runs on each
sheet. I would like to calculate some data and store it on each sheet
as a named variable.

For example on sheet1 the macro calculates x = 1 and y = 2, but on
sheet2 x=4 and y=3.

I want another macro that runs through all the sheets calculating x+y
for each sheet.

How can I have do this?

Thanks.
 
You are describing a native function of Excel, it's called a worksheet
function. Instead of coding variables and making your life a nightmare with
them, just enter these 'variables' as a function on each sheet. Then
diseminate them on your final sheet. It is made easier if you keep all the
sheet formulas in the same cell (address) on each sheet.
 
keyser,

Much better to have the first macro calculate x+y while the variables are still active.

Otherwise, you could store those values in an un-used cell. In the first macro:

Worksheets(i).Range("A100").Value = x
Worksheets(i).Range("A101").Value = y

Then the second macro could use:

x = Worksheets(i).Range("A100").Value
y = Worksheets(i).Range("A101").Value

That assumes that you are using i as your indexing variable...

HTH,
Bernie
MS Excel MVP
 
when I am in the workbook macro and I want to refer to the current
sheet, how can I do this if I do not know the current index? (or how do
I calculate the index?)

Worksheets(i).Range("A100").Value = x
 
If you are in the current sheet, just use ..

Range(...

You can always say ..

Activesheet.Range(...

But that is not necessary. If programming from another location, i.e.
ThisWorkbook module, you can use ..

ActiveSheet.Index

But I believe, if doing such, it will have the worksheet as a variable for
you already, 'Sh' I believe. So it would only be ..

Sh.Index

HTH
 
Back
Top