macro referring to a previous sheet

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi,
I'm sorry if this is a silly question, but I can't suss it
out. I've a workbook with about 200 sheets in it. Each
sheet's tab is named. I'm trying to create a macro which
will add 7 to the cell in a previous sheet. I want to use
the macro on every sheet and have it refer to the sheet
before, but when I try to use it it always refers to the
named sheet it used when I created it (I can't do VBA, so
I create my macros using the recorder). The line I get
referring to the named sheet is like this:
ActiveCell.FormulaR1C1 = "=wk03.01!RC+7"
I've tried to use the 'Relative/Absolute' button on the
macro recorder but it makes no difference. Is there a way
I can get the macro to look at the previous sheet rather
than always looking at the sheet named 'wk03.01', please?
Thanks in anticipation...
Mike
 
You can use the sheet index number to get the name of the previous sheet
to use later.

pSName = Worksheets(ActiveSheet.Index - 1).Name
MsgBox pSName
 
Back
Top