Macro to increase cell ref in formula by 1

[David Beattie]

Hi

I am working on a weekly report in which I have a summary sheet that references several cells in in each sheet, I have a formula to pick up the cell data I need but have to manually update the ref the cell below each time I run it, can anyone help me with a macro to increase the cell ref in the formula by 1 so I can attach it to a button?

Thanks

 

[steve the fish]

What does the formula look like? Why is the increase of 1 happening?

 

[David Beattie]

The current formula is =SHSC!G35

The information on each sheet are performance stats for the previous week which are sent to managers

 

[steve the fish]

Ok so whats the formula in SHSC!G35

 

[David Beattie]

Ok so whats the formula in SHSC!G35

Hi the formula is just pulling information from a cell on another sheet, there are about 50 cells that are pulled from various sheets so its just a way to click a button and it move all the refs down 1

 

[VoG]

Maybe like this with the cell selected

Sub a()
With ActiveCell
    .Formula = Left(.Formula, 7) & Right(.Formula, Len(.Formula) - 7) + 1
End With
End Sub

 

[Peter_SSs]

Hi the formula is just pulling information from a cell on another sheet ..

If that is all it is doing then you could also try something like ..

With ActiveCell
  .Formula = Split(.Formula, "!")(0) & "!" & Range(Split(.Formula, "!")(1)).Offset(1).Address
End With