Copy cells to the left of an event in a cell

[Jacob M]

I need some help writing code. When I type "d" in any cell on the
spreadsheet, I want excel to initiate a macro that will automatically copy
the values of the two cells to the left of it.

For example: if I type "d" and then enter in cell C3, I want it to
automatically copy the range A1:A2. OR if I type "d" and then enter in cell
F7, I want it to automaticall copy the range G7:E7. Any combo like that
should work.

The last step I can do, which is pasting special values in the next
available cell of a different sheet.

 

[p45cal]

I need some help writing code. When I type "d" in any cell on the
spreadsheet, I want excel to initiate a macro that will automaticall
copy
the values of the two cells to the left of it.

For example: if I type "d" and then enter in cell C3, I want it to
automatically copy the range A1:A2. OR if I type "d" and then enter i
cell
F7, I want it to automaticall copy the range G7:E7. Any combo lik
that
should work.

The last step I can do, which is pasting special values in the next
available cell of a different sheet.

Paste this into the relevant sheet's cod
modulerivate Sub Worksheet_Change(ByVal Target A
Range)
If Target.Cells.Count = 1 And Target.Value = "d" And Target.Column >
Then
Target.Offset(, -2).Resize(, 2).Copy
End If
End Sub
but check that > If I type "d" and then enter in cell C3, I want it to automatically cop

the range A1:A2 should read:

If I type "d" and then enter in cell C3, I want it to automaticall
copy the range A1:*B1

 

[Jacob M]

So I typed:

Sub Draft()
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count=1 And Target.Value="d" And Target.Column>2
Then
Target.Offset(, -2).Resize(, 2).Copy
End If
End Sub

But the compiler doesn't like: "If Target.Cells.Count=1 And Target.Value="d"
And Target.Column>2". Any ideas?

Jacob M

 

[p45cal]

You're probably pasting the code into the wrong module and you don'
need to start it with *Sub Draft()*
Select the sheet you want it to work in, right-click its tab and choos
*View code..*, the vbe will open with the flashing cursor where you nee
to paste the code.
Go back to the sheet and test

 

[Jacob M]

That worked. I thought I could combine the two steps, but I'm having trouble.
So now when I type "d" and enter, it copies the cells to the left. I then hit
ctrl+d and it pastes the values in the next available cell in a different
sheet. Is there anyway to combine the two?

Sheet1 Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Value = "d" And Target.Column > 2 Then
Target.Offset(, -2).Resize(, 2).Copy
End If
End Sub

Module1 Code:
Sub DraftPlayer()
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Team Roster").Select
Range("C2").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.PasteSpecial (xlPasteValues)
ActiveWorkbook.Sheets("Team Roster").Activate
Application.CutCopyMode = False
End Sub

Thanks,
Jacob M

 

[p45cal]

That worked. I thought I could combine the two steps, but I'm having
trouble.
So now when I type "d" and enter, it copies the cells to the left. I
then hit
ctrl+d and it pastes the values in the next available cell in a
different
sheet. Is there anyway to combine the two?Well it worked here fine. Are these sheets all in the same workbook?

By the way, a little improvementrivate Sub
Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Value = "d" And Target.Column > 2 Then
Target.Offset(, -2).Resize(, 2).Copy
End If
End Sub
eliminates a bug when you changed more than one cell at
once on Sheet1.

If you want you could avoid having to do ctrl+d by putting everything
in the event coderivate Sub Worksheet_Change(ByVal
Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Value = "d" And Target.Column > 2 Then
Set Source = Target.Offset(, -2).Resize(, 2)
Set Dest = Sheets("Team Roster").Range("C2")
Do
If Not IsEmpty(Dest) Then
Set Dest = Dest.Offset(1)
End If
Loop Until IsEmpty(Dest)
Set Dest = Dest.Resize(, 2)
Dest.Value = Source.Value
End If
End Subwhich also avoids lots of sheet swapping and
selecting.