copy & paste to all Sheets to the same cells

M

Martyn

Hi,
The below macro copies a value entered to Sheet1 A1 to other two Sheets
A1's.
Can I generalize it so that it is adoptable to "any" cell on Sheet1?. I need
to be able to automatically copy any value entered to Sheet1' to the same
cell on all other Sheets!. (e.g.: if I write a number or a text to Sheet1
K4, I want excel to automatically copy the same value to all existing Sheets
K4' cells.
Here is my macro:
------------------------------------------
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 22.06.2004 by Martyn
'

'
ActiveCell.FormulaR1C1 = "3"
Range("A1").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
ActiveSheet.Paste
End Sub
 
S

Simon Lloyd

Heres some code i used, substitute your sheet names for mine, for ever
sheet that you paste this in to (worksheet_selection Change) the nam
of that particular sheet must appear first in the array, all you hav
to do is set the range for it to work on, by the way mine works off
userform that looks up a list on a hidden sheet (create your userfor
and then in the properties set the row source to the list name on th
hidden sheet.

This may not be exactly what you want but with some trimming an
adaptation it will do what you want. Where you see text in capitals
have added comments to try to help

Simon

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sh As Object
Dim myrange As Range
Dim ComboBox1
Dim I1 As Integer
Dim res As Variant
Dim arySheets
On Error Resume Next



With arySheets
'THE CELL RANGE IS SET HERE
Set myrange = Range("E3:H200")
If Not Intersect(myrange, Target) Is Nothing Then
'THIS SELECTS ALL SHEETS THAT ARE LISTED
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
arySheets = Array("Craft Areas", "Plant Awareness", _
"Vehicle Drivers (plant)")

Sheets(arySheets).Select


For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect
Next
End If
'HERE I HAVE SET THE RANGE THAT THE USERFORM WILL WORK ON OR I
WILL POP UP EVERYWHERE
If ActiveCell.Column >= 5 And ActiveCell.Column <= 8 An
ActiveCell.Row >= 3 And ActiveCell.Row <= 200 Then
UserForm1.Show
If Not IsError(res) Then

ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
'THIS LINE TELLS IT TO MAKE SURE THE HIDDEN SHEET IS NO
VISIBLE OTHERWISE IT WILL BE SELECTED AND CAUSE AN ERROR
Worksheets("hidden").Visible = False
Me.Select

End If
'HERE IT IS TOLD SHOULD IT NOT BE A CERTAIN COLUMN NAME THEN G
TO COLUMN A SAME ROW
If ActiveCell <> "shift " Then
Range("A" & ActiveCell.Row).Select
'THE NEXT LINE TAKES YOU BACK TO THE FIRST SHEET IN TH
ARRAY
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst

End If
End If

End With


End Su
 
M

Martyn

Thank you Simon, this is very kind of you.
But as far as I can see this is far too complex then I thought...
All I need is to be able to copy&paste automatically to all other Sheets to
the same cells. I'll appreciate if modification suggestions to my code can
be given.
Martyn
 
Top