Selection.Copy not working

[overbet]

Does anyone have any idea why I am not able to get this macro to copy cell data?
Here is the last part of the code:

  SendKeys "~"
'presses enter to move down one cell
  SendKeys "^{k}"
 'runs macro shortcut
  
  Call Copy_Location
End Sub
Sub Copy_Location()
'moves right 3 cells
    Application.SendKeys "{TAB}"
    Application.SendKeys "{TAB}"
    Application.SendKeys "{TAB}"
    Selection.Copy
    'SendKeys "^{c}"
End Sub

I am not very good at VBA, but this is what I am trying to do here. I put this piece of code at the end of a macro I am using regularly to cut a couple manual steps out of my work. The code needs to go down one cell (SendKeys "~"), the it runs a another macro shortcut (SendKeys "^{k}"), then it calls the rest that moves the selected cell 3 cells to the right, then finally it is supposed to copy that cells data so I can manually paste it elsewhere. This works for all the steps except the final step of copying the cell data. I tired Selection.copy and I tried SendKeys "^{c}". Neither work for me.

Like I said I am not good with vba so I am sure this a convoluted method and there is a better way. This works for every step except the last step. If I comment out the last step, the copy, it goes right to the cell I need the data from. If I leave the copy step in, it doesnt get past the first step of pressing the enter key. Thanks for any suggestions.

 

[MARK858]

Possibly remove

'presses enter to move down one cell
  SendKeys "^{k}"
 'runs macro shortcut

and change the other sub to

Sub Copy_Location()
'copies cell 1 down and right 3 cells
ActiveCell.Offset(1, 3).Copy
End Sub

 

[footoo]

Maybe :

Selection(2).Select
'move down one cell
Call MacroName
'run macro
Selection(1, 3).Copy
'copy cell 3 cells right of selection

 

[overbet]

Thank you both. These solutions both seem to copy cell which I wasnt able to do before. I just need to tweak it a little, but I should be able to get it from here. Thanks again Mr. Excel ninjas!

 

[MARK858]

Thank you both. These solutions both seem to copy cell which I wasnt able to do before.!

Strange that you are getting the same result for both codes as there should be one column difference

 

[overbet]

No, not same result. They both got the copy cell to work which Ive been struggling with. I was able to get both ways to work with tweaks and I actually had to put you twos parts of the code at the end of the macro being called instead of at the end of the macro that was doing the calling. Again, thanks it now works exactly how I need. I been fighting with it for a couple days and now it works 15 minutes after getting your help. Cheers!

 

[footoo]

Strange that you are getting the same result for both codes as there should be one column difference

Yes indeed. My code should have read : Selection(1, 4).Copy

(But it depends whether or not there is a macro being run between the move down one cell and the cell.copy. And what cell the macro selects.)

 

[MARK858]

and I actually had to put you twos parts of the code at the end of the macro being called instead of at the end of the macro that was doing the calling

I don't understand why you needed to put them in the same macro as the below works fine for me...

Sub ddddd()
Range("a1").Select
Copy_Location
End Sub

Sub Copy_Location()
ActiveCell.Offset(1, 3).Copy
End Sub

as does...

Sub ddddd2()
Range("a1").Select
Selection(2).Select
Copy_Location2
End Sub

Sub Copy_Location2()
Selection(1, 4).Copy
End Sub

 

[footoo]

I don't understand why you needed to put them in the same macro as the below works fine for me...

I think there is a macro being run before the copy code, which probably changes the selection.

  SendKeys "~"
'presses enter to move down one cell
[COLOR=#ff0000]  SendKeys "^{k}"
 'runs macro shortcut[/COLOR]
  
  Call Copy_Location
End Sub
Sub Copy_Location()
'moves right 3 cells
    Application.SendKeys "{TAB}"
    Application.SendKeys "{TAB}"
    Application.SendKeys "{TAB}"
    Selection.Copy
    'SendKeys "^{c}"
End Sub

 

[MARK858]

I think there is a macro being run before the copy code, which probably changes the selection.

Yep, overlooked that.... thanks for pointing it out