2 problems with copying cells to sheets in same workbook

  • Thread starter Thread starter BillD
  • Start date Start date
B

BillD

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.
 
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
 
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
 
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
Click to expand...

Thanks in advance for all your help
 
Back
Top