A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

copy & paste to all Sheets to the same cells



 
 
Thread Tools Display Modes
  #1  
Old June 22nd 04, 06:13 PM posted to microsoft.public.excel.programming
Martyn
external usenet poster
 
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


Ads
  #2  
Old June 22nd 04, 07:10 PM posted to microsoft.public.excel.programming
Simon Lloyd[_498_]
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

  #3  
Old June 23rd 04, 06:42 AM posted to microsoft.public.excel.programming
Martyn
external usenet poster
 
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 01:33 AM.


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