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?

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

--
Don Guillett
SalesAid Software

"Pat" wrote in message
...
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?

or programmatically you could use

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



"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Pat" wrote in message
...
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?

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

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

--
Don Guillett
SalesAid Software

"Pat" wrote in message
...
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?

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" wrote:

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

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

--
Don Guillett
SalesAid Software

"Pat" wrote in message
...
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?

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.


"ben" wrote in message
...
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" wrote:

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

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

--
Don Guillett
SalesAid Software

"Pat" wrote in message
...
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?

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" wrote:

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.


"ben" wrote in message
...
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" wrote:

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

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

--
Don Guillett
SalesAid Software

"Pat" wrote in message
...
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?

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.


"ben" wrote in message
...
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" wrote:

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.


"ben" wrote in message
...
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" wrote:

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

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

--
Don Guillett
SalesAid Software

"Pat" wrote in message
...
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?

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

"Pat" wrote:

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.


"ben" wrote in message
...
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" wrote:

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.


"ben" wrote in message
...
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" wrote:

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

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

--
Don Guillett
SalesAid Software

"Pat" wrote in message
...
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?

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.

--
Regards,
Tom Ogilvy


"ben" wrote in message
...
or programmatically you could use

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



"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Pat" wrote in message
...
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?

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?

--
Tom Ogilvy

"Pat" wrote in message
...
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.


"ben" wrote in message
...
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" wrote:

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

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

--
Don Guillett
SalesAid Software

"Pat" wrote in message
...
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?

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


"Tom Ogilvy" wrote in message
...
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?

--
Tom Ogilvy

"Pat" wrote in message
...
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.


"ben" wrote in message
...
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" wrote:

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

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

--
Don Guillett
SalesAid Software

"Pat" wrote in message
...
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?