My spreadsheet has 5 sheets. I want the contents of cell A1 to
be repeated on all 5 sheets.

So, for example, if a user changes A1 to "Claus" on Sheet 4
then the change should update A1 on all sheets. Or, if someone changes
A1 to "GS" on Sheet 1, then the change should update all sheets, etc....

I'm interested in all methods possible, whether using VBA code or
sheet formulas. (I might have asked a similar question long ago, so I
apologize if I repeated.)

Happy Halloween all!

Hi Robert,

Am Sat, 31 Oct 2015 15:42:41 -0700 schrieb Robert Crandal:

My spreadsheet has 5 sheets. I want the contents of cell A1 to
be repeated on all 5 sheets.

select Sheet2 and with pressed Shift button Sheet5.
Enter in A1 the formula:
=Sheet1!A1

Or right click on sheet tab of Sheet1 = Show Code and paste following
code into the code window:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) < "A1" Then Exit Sub

Dim i As Long

For i = 2 To 5
Sheets(i).Range("A1") = Target
Next
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Robert,

Am Sat, 31 Oct 2015 15:42:41 -0700 schrieb Robert Crandal:

So, for example, if a user changes A1 to "Claus" on Sheet 4
then the change should update A1 on all sheets. Or, if someone changes
A1 to "GS" on Sheet 1, then the change should update all sheets, etc....

sorry, I misunderstood your problem. If you want to change A1 for all
sheets from each sheet then try the following code in the
Workbook_SheetChange event:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address(0, 0) < "A1" Then Exit Sub

Dim i As Long
Dim ShN As String

Application.EnableEvents = False
On Error GoTo CleanUp

ShN = Sh.Name
For i = 1 To 5
If Sheets(i).Name < ShN Then
Sheets(i).Range("A1") = Target.Value
End If
Next
CleanUp:
Application.EnableEvents = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

"Claus Busch" wrote:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Address(0, 0) < "A1" Then Exit Sub

Dim i As Long
Dim ShN As String

Application.EnableEvents = False
On Error GoTo CleanUp

ShN = Sh.Name
For i = 1 To 5
If Sheets(i).Name < ShN Then
Sheets(i).Range("A1") = Target.Value
End If
Next
CleanUp:
Application.EnableEvents = True
End Sub


Thanks Claus. That's what I'm looking for.

Can the above solution also be implemented with just cell formulas?
Just curious.

Hi Robert,

Am Sun, 1 Nov 2015 02:50:20 -0700 schrieb Robert Crandal:

Can the above solution also be implemented with just cell formulas?

no, because you can have either formulas OR values in a cell but not
both.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional