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 <Bi...@discussions.microsoft.com> 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
>
> .
>
|
Similar Threads
