View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Simon Lloyd[_498_] Simon Lloyd[_498_] is offline
external usenet poster
 
Posts: 1
Default copy & paste to all Sheets to the same cells

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

--
Message posted from http://www.ExcelForum.com