Insert the same value in other sheets

[Pat]

Hi,
If Sheet1!A1 = 100
then Sheet2!A1 = 100, Sheet3!A1 = 100, Sheet4!A1 = 100

I do not want to use a formula preferring to execute via a commandbutton.

Any ideas?

 

[Don Guillett]

Just use the control or shift key to select the sheets desired. then put 100
in a1. Excel will put it in all

 

[Guest]

or programmatically you could use

Sheets(Array("Sheet3", "Sheet2", "Sheet1")).Select
Sheets("Sheet3").Activate
range("a100").value = "100"

 

[Pat]

Thank you for your advice but I want to execute it by code. I only used an
example to describe what I wish to do. I do not want to go into the other
sheets to look for the cells in question.

Pat

 

[Guest]

of course if you don't want to write an enormous array line if you have lots
of sheets you could use

sub samevalue ()
for each w in activeworkbook.sheets
w.range("a100").value = 100 ' or whatever value
next
end usb

 

[Pat]

Almost there!
The value in Sheet1!A1 will change frequently consequently the code will
need to first recognize the value then replicate it in the other sheets. The
should mention, the other sheets (in the live workbook it will be used in)
will have a different cell range for the value change to.

 

[Guest]

you can enter a sub in the worksheet level object in vba


private sub worksheet_change(Byval Target as Address)
if target.row = 'put target row here and target.column = 'put target column
here (in a number not a letter) then
now insert your change code with the change of target.value instead of
range("a1").value

end if
end sub

 

[Pat]

Correct me if I am wrong, by using

private sub worksheet_change(Byval Target as Address)

will had over control to the sheet and any changes will take place
automatically?

If this is the case I prefer not to go down this route, but instead execute
code as and when desired.

 

[Guest]

yes it will do so automatically, however if you wish to do so as desired
you can do many things, you can assign macro to a shortcut key, to a command
button, call it from another macro, you can assign it to a menu button, or
even put it on a userform

 

[Tom Ogilvy]

Ben,
VBA doesn't provide much support for grouped sheets. If you run your code,
you will see only the activesheet (sheet3) gets the value of 100.

You can trick Excel with some things however, by selecting the cell.

Sub Tester1()
Sheets(Array("Sheet3", "Sheet2", "Sheet1")).Select
Sheets("Sheet3").Activate
Range("a100").Select
Selection.Value = "100"

End Sub

The above modification will put the value in all 3 sheets.

 

[Tom Ogilvy]

Are you saying if a change is made anywhere it one sheet it should be
replicated in all sheets.

If not, what are you saying? Is it only if a change is made in a specific
cell; as specific set of cells?

 

[Pat]

Sorry for the lack of clarity.
I have in the meantime worked out what I need and here it is:

Private Sub ReplicateV_Click()
With Me.Range("K23")
..Value = .Value
Worksheets("Invref").Range("E23").Value = .Value
Worksheets("CaSS").Range("K18").Value = .Value
Worksheets("CaSE").Range("K18").Value = .Value
End With
End Sub

Thank you gentlemen for your contributions.
Pat