Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 22nd 04, 06:13 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2004
Posts: 19
Default 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   Report Post  
Old June 22nd 04, 07:10 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2004
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

  #3   Report Post  
Old June 23rd 04, 06:42 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2004
Posts: 80
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy&paste of several sheets Lorenz Excel Discussion (Misc queries) 1 May 29th 07 10:08 PM
Copy cells from one worksheet and paste to incrementing sheets? [email protected] Excel Discussion (Misc queries) 2 February 19th 07 11:35 AM
Auto "copy and paste" individual cells from various sheets into one sheet ?? [email protected] Excel Discussion (Misc queries) 2 March 1st 06 03:19 AM
copy from one sheet and paste into other sheets TUNGANA KURMA RAJU Excel Discussion (Misc queries) 3 December 8th 05 02:49 PM
MS Excel Sheets...how to copy and paste ? tina SPEILBERG Excel Programming 1 August 4th 03 07:15 AM


All times are GMT +1. The time now is 08:02 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright ©2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017