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.

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

So I typed:

Sub Draft()
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count=1 And Target.Value="d" And Target.Column2
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.Column2". 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

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

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

Jacob M;466008 Wrote:
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 improvement:Private 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 code:Private 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.


--
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