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.

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" wrote:

>
> Jacob M;465624 Wrote:
> > 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.
>
> Paste this into the relevant sheet's code
> module: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 check that > If I type "d" and then enter in cell C3, I want it to automatically copy
> > the range A1:A2 should read:
> If I type "d" and then enter in cell C3, I want it to automatically
> copy the range A1:*B1*
>
>
> --
> p45cal
>
> *p45cal*
> ------------------------------------------------------------------------
> p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=128785
>
>

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" wrote:

>
> You're probably pasting the code into the wrong module and you don't
> need to start it with *Sub Draft()*
> Select the sheet you want it to work in, right-click its tab and choose
> *View code..*, the vbe will open with the flashing cursor where you need
> to paste the code.
> Go back to the sheet and test.
>
>
> --
> p45cal
>
> *p45cal*
> ------------------------------------------------------------------------
> p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=128785
>
>