VBA: How to change cell value in another sheet based on the active sheet

[davey4444]

Hi there,

I think that my thread title sums it up but essentially all that I want to do is to change the value of cell A1 in Sheet1 to whatever my active sheet is in the same workbook. Ideally I would like this to update automatically whenever I change sheets however if a button on every other sheet is needed then I can live with that.

Thanks.

 

[JoeMo]

Not sure I understand your request. You want Sheet1 cell A1 value to be the same as cell A1 of the active sheet or ...?

 

[davey4444]

Thanks for the quick reply. Essentially I would like A1 in Sheet1 to hold the value of whatever Active Sheet I am in i.e the name of the active sheet rather than the value of any cell within it.

 

[Momentman]

I am not sure if you want sheet1 to reflect if its the active sheet

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If ActiveSheet.Name <> "Sheet1" Then
        Sheet1.Range("A1").Value = ActiveSheet.Name
    End If
End Sub

or without the if (applies to all sheets including sheet1)

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
          Sheet1.Range("A1").Value = ActiveSheet.Name
End Sub

 

[JoeMo]

Thanks for the quick reply. Essentially I would like A1 in Sheet1 to hold the value of whatever Active Sheet I am in i.e the name of the active sheet rather than the value of any cell within it.

See post #4 from Momentman.