Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy & paste to all Sheets to the same cells
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 --------------------------------------------- TIA Martyn --- Outgoing mail is certified Virus Free. (Giden posta virüssüz olarak belgelendi.) Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.708 / Virus Database: 464 - Release Date: 18.06.2004 --- Outgoing mail is certified Virus Free. (Giden posta virüssüz olarak belgelendi.) Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.708 / Virus Database: 464 - Release Date: 18.06.2004 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy & paste to all Sheets to the same cells
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy&paste of several sheets | Excel Discussion (Misc queries) | |||
Copy cells from one worksheet and paste to incrementing sheets? | Excel Discussion (Misc queries) | |||
Auto "copy and paste" individual cells from various sheets into one sheet ?? | Excel Discussion (Misc queries) | |||
copy from one sheet and paste into other sheets | Excel Discussion (Misc queries) | |||
MS Excel Sheets...how to copy and paste ? | Excel Programming |