VBA to populate cell A2 with cell A2 from previously select WKST.

[KP_SoCal]

I have an excel workbook with tabs: Sheet1, Sheet2, Sheet3, Sheet 4, Sheet 5, Sheet 6, and Sheet7.

For Sheets 1 thru 5, I need cell A2 on a newly activated sheet to populate with the value on the most recent worksheet. Sheet1 holds the default value of “Widget1” in A2. So if I click on any Sheet between 2 and 5 (Sheets 6 and 7 should be excluded in criteria), cell A2 would populate with “Widget1”.

However, if I decide to populate cell A2 on Sheet3 with “Widget3”, then if I click back to Sheet1 it would populate with “Widget3” in call A2. Does this make sense?

I’ll probably need something like this code below, but I’m just not sure how to piece it together to make it work in the way I described above. Any advice is greatly appreciated.

Dim wsSheet As Worksheet
Application.ScreenUpdating = False
For Each wsSheet In ThisWorkbook.Worksheets
    If wsSheet.CodeName =        Then
        
    Else
        
    End If
Next wsSheet
Application.ScreenUpdating = True

 

[machopicho]

Your problem is like a dog chasing its tail ...
When editing a cell A2 sheets 2 to 5 and you active sheet 1 find modify, but when you switch back to 2 to 5 sheets of A2 of the modified sheet 1 is extended to other sheets. Anyway:
In workBook:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
For i = 2 To 5
       Worksheets(i).Range("A2") = Worksheets(1).Range("A2")
   Next
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
If Target <> 0 Then
   For i = 2 To 5
       Worksheets(1).Range("A2") = Target
   Next
End If
End Sub

Bye

 

[KP_SoCal]

Your problem is like a dog chasing its tail ...

Ha, ha! Tell me about it.

Hey, thanks for this suggestion. I think I can make that work into what I need. Cheers!

KP

 

[mikerickson]

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    If Sh.Name <> "Sheet1" Then
        Worksheets("Sheet1").Range("A2").Value = Sh.Range("A2").Value
    End If
End Sub

 

[KP_SoCal]

Mike, I like it! This will be cleaner and I like that it uses the deactivation event since I'm already running other procedures for the activation effect. Thanks you so much!!!

KP