Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 problems with copying cells to sheets in same workbook
I have the following code to copy cells from one worksheet to another. I am
using this as I want the data to copy automatically when I hit either enter, tab, or mouse click: Private Sub Worksheet_Change(ByVal Target As Range) ' Copies Pay Period from sheet 1 to cover ' If Not Intersect(Target, Me.Range("R9")) Is Nothing Then Me.Range("R9").copy Destination:=Sheets("Cover").Range("H6") End If End Sub Problem 1 is the fonts on one worksheet are larger (16) so I can view the data as I reduced the view percentage so I could get all of the sheet in the window. I need to copy just the values/data from the cell and not the format. I created a macro to see the code and I have the following but do not know where to put it in the above code: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False When I insert this code into the above code to copy only the data I get a syntx error. Could you tell me where I should place this code in the above code or if I need something different what that code would be and where would I put it. Problem 2 is I have several different cells that I need to copy on all work sheets if I enter the above Private Sub code I get an error that there is a conflict with the other routines that use the same Private Sub Worksheet_Change(ByVal Target As Range) at least that is the part that is highlighted in yellow. How can I have this code to copy other cells from one worksheet to another with other routines using the same code but different cell ranges. -- BillD |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 problems with copying cells to sheets in same workbook
Hi
The PasteSpecial is not a part of the copy statement, so first you copy Target cell, and in next line use paste special to past in desired cell. As you have seen, you can only have one Worksheet_Change event, but you can do multiple things in this code. If Target does not intersect with cell A, then test if Target intersect with cell B.... Private Sub Worksheet_Change(ByVal Target As Range) 'Copies Pay Period from sheet 1 to cover ' If Not Intersect(Target, Me.Range("R9")) Is Nothing Then Me.Range("R9").Copy Sheets("Cover").Range("H6").PasteSpecial _ Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ElseIf Not Intersect(Target, Range("A1,B5,H1:H10")) Is Nothing Then 'Copy to all sheets Target.Copy For sh = 1 To Sheets.Count If Sheets(sh) < ActiveSheet.Name Then ActiveSheet.Paste Sheets(sh).Range("A1") End If Next Application.CutCopyMode = False End If End Sub Regards, Per On 23 Okt., 21:03, BillD wrote: I have the following code to copy cells from one worksheet to another. I am using this as I want the data to copy automatically when I hit either enter, tab, or mouse click: Private *Sub Worksheet_Change(ByVal Target As Range) ' Copies Pay Period from sheet 1 to cover ' If Not Intersect(Target, Me.Range("R9")) Is Nothing Then Me.Range("R9").copy Destination:=Sheets("Cover").Range("H6") End If End Sub Problem 1 is the fonts on one worksheet are larger (16) so I can view the data as I reduced the view percentage so I could get all of the sheet in the window. I need to copy just the values/data from the cell and not the format. I created a macro to see the code and I have the following but do not know where to put it in the above code: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ * * *:=False, Transpose:=False When I insert this code into the above code to copy only the data I get a syntx error. Could you tell me where I should place this code in the above code or if I need something different what that code would be and where would I put it.. Problem 2 is I have several different cells that I need to copy on all work sheets if I enter the above Private Sub code I get an error that there is a conflict with the other routines that use the same Private Sub Worksheet_Change(ByVal Target As Range) at least that is the part that is highlighted in yellow. How can I have this code to copy other cells from one worksheet to another with other routines using the same code but different cell ranges. -- BillD |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 problems with copying cells to sheets in same workbook
For problem 1 go the other way 'round to just get the value.
Private Sub Worksheet_Change(ByVal Target As Range) ' 'Copies Pay Period from sheet 1 to cover ' If Not Intersect(Target, Me.Range("R9")) Is Nothing Then Sheets("Cover").Range("H6").Value = Target.Value End If End Sub BTW..........A single mouse-click won't trigger the event...........Tab or Enter will. For the second problem...........as you have found out you cannot have duplicate event types in one sheet module. You must combine your needs into one change event. Cannot advise how since you have not given any details. Gord Dibben MS Excel MVP On Fri, 23 Oct 2009 12:03:01 -0700, BillD wrote: I have the following code to copy cells from one worksheet to another. I am using this as I want the data to copy automatically when I hit either enter, tab, or mouse click: Private Sub Worksheet_Change(ByVal Target As Range) ' Copies Pay Period from sheet 1 to cover ' If Not Intersect(Target, Me.Range("R9")) Is Nothing Then Me.Range("R9").copy Destination:=Sheets("Cover").Range("H6") End If End Sub Problem 1 is the fonts on one worksheet are larger (16) so I can view the data as I reduced the view percentage so I could get all of the sheet in the window. I need to copy just the values/data from the cell and not the format. I created a macro to see the code and I have the following but do not know where to put it in the above code: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False When I insert this code into the above code to copy only the data I get a syntx error. Could you tell me where I should place this code in the above code or if I need something different what that code would be and where would I put it. Problem 2 is I have several different cells that I need to copy on all work sheets if I enter the above Private Sub code I get an error that there is a conflict with the other routines that use the same Private Sub Worksheet_Change(ByVal Target As Range) at least that is the part that is highlighted in yellow. How can I have this code to copy other cells from one worksheet to another with other routines using the same code but different cell ranges. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 problems with copying cells to sheets in same workbook
I am having some problems with the code you gave me and I am not totally
understanding the second part of the code the ElseIF part. Here is exactly what I am trying to do: I want to enter data (date) in Sheets("Sheet1").Range("R9") When I hit the enter key I want it to copy to: Sheets("Cover").Range("H6") Sheets("Sheet2").Range("S17") Sheets("Sheet3").Range("S17") Sheets("Sheet4").Range("V20") The fonts in sheets("Cover") and Sheets("sheet3) are different than the rest of the fonts so I only want to copy the values which I understand is where the PasteSpecial Past.......... comes in. If you could help me get this right I would greatly appreciate it Again here is what you gave me: If Not Intersect(Target, Me.Range("R9")) Is Nothing Then Me.Range("R9").Copy Sheets("Cover").Range("H6").PasteSpecial _ Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ElseIf Not Intersect(Target, Range("A1,B5,H1:H10")) Is Nothing Then 'Copy to all sheets Target.Copy For sh = 1 To Sheets.Count If Sheets(sh) < ActiveSheet.Name Then ActiveSheet.Paste Sheets(sh).Range("A1") End If Next Application.CutCopyMode = False End If End Sub Thanks in advance for all your help -- BillD "Per Jessen" wrote: Hi The PasteSpecial is not a part of the copy statement, so first you copy Target cell, and in next line use paste special to past in desired cell. As you have seen, you can only have one Worksheet_Change event, but you can do multiple things in this code. If Target does not intersect with cell A, then test if Target intersect with cell B.... Private Sub Worksheet_Change(ByVal Target As Range) 'Copies Pay Period from sheet 1 to cover ' If Not Intersect(Target, Me.Range("R9")) Is Nothing Then Me.Range("R9").Copy Sheets("Cover").Range("H6").PasteSpecial _ Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ElseIf Not Intersect(Target, Range("A1,B5,H1:H10")) Is Nothing Then 'Copy to all sheets Target.Copy For sh = 1 To Sheets.Count If Sheets(sh) < ActiveSheet.Name Then ActiveSheet.Paste Sheets(sh).Range("A1") End If Next Application.CutCopyMode = False End If End Sub Regards, Per On 23 Okt., 21:03, BillD wrote: I have the following code to copy cells from one worksheet to another. I am using this as I want the data to copy automatically when I hit either enter, tab, or mouse click: Private Sub Worksheet_Change(ByVal Target As Range) ' Copies Pay Period from sheet 1 to cover ' If Not Intersect(Target, Me.Range("R9")) Is Nothing Then Me.Range("R9").copy Destination:=Sheets("Cover").Range("H6") End If End Sub Problem 1 is the fonts on one worksheet are larger (16) so I can view the data as I reduced the view percentage so I could get all of the sheet in the window. I need to copy just the values/data from the cell and not the format. I created a macro to see the code and I have the following but do not know where to put it in the above code: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False When I insert this code into the above code to copy only the data I get a syntx error. Could you tell me where I should place this code in the above code or if I need something different what that code would be and where would I put it.. Problem 2 is I have several different cells that I need to copy on all work sheets if I enter the above Private Sub code I get an error that there is a conflict with the other routines that use the same Private Sub Worksheet_Change(ByVal Target As Range) at least that is the part that is highlighted in yellow. How can I have this code to copy other cells from one worksheet to another with other routines using the same code but different cell ranges. -- BillD . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying cells from one workbook to another with many sheets | Excel Programming | |||
Copying cells between sheets in a workbook | Excel Discussion (Misc queries) | |||
HELP= Problems Copying WorkBook Sheets | Excel Programming | |||
Copying data from workbook/sheets to another workbook/sheet | Excel Programming | |||
Problems copying cells using offset and counta | Excel Discussion (Misc queries) |