Macro question, Copy-Paste

[MrDave]

hi, I am trying to pick columns from a keyboard short cut with macro located
under a General Module..? but I get an: Invalid use of Me keyword. is
there a way to make this work? thanks.

Sub Paste2() 'alt-/ (slash)
Dim M2 As String
M2 = Range("M2")
Dim N3 As String
N3 = Range("N3")

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=190
ActiveCell.Offset(190, 0).Select

If Not Intersect(Me.Range(M2), .Cells) Is Nothing Then
With Me.Cells(.Row, N3).Select
'.Offset(0, 0).Select
End With
End If

'With Me.Cells(.Row, N3).Select
'Range(ActiveCell, ActiveCell.Offset(190, 0)).Copy

End Sub

 

[Luke M]

Replace "Me" with "ActiveSheet", since you are calling the macro via keyboard
shortcuts, and aren't changing worksheets.

 

[Jim Thomlinson]

Me refers to the parent object. Since you are in a standard module there is
no parent object.

Replace me with ActiveSheet.

also you have unqulified references. You have
If Not Intersect(Me.Range(M2), .Cells) Is Nothing Then
but the .Cells is not part of a with statement so it is unreferenced. Once
again activesheet will do.

 

[MrDave]

hi, thanks, I gave something a try.., but not working, not sure of the syntax
for..

Sub Paste2() 'alt-/ (slash)
Dim M2 As String
M2 = Range("M2")
Dim N3 As String
N3 = Range("N3")

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=190

ActiveSheet(.Row, N3).Select 'not working
Range(ActiveSheet, ActiveCell.Offset(190, 0)).Copy


'If Not Intersect(ActiveSheet.Range(M2)) Is Nothing Then 'wrong
' With ActiveSheet(.Row, N3).Select
' '.Offset(0, 0).Select
' End With
'End If

'If Not Intersect(Me.Range(M2), .Cells) Is Nothing Then 'wrong
' With Me.Cells(.Row, N3).Select
' '.Offset(0, 0).Select
' End With
'End If

'With Me.Cells(.Row, N3).Select
'Range(ActiveCell, ActiveCell.Offset(190, 0)).Copy

End Sub

 

[Jim Thomlinson]

Perhaps you could just tell us what you are trying to do... From just reading
your code it is a bit hard to tell.

 

[MrDave]

I see, just automating, if ultimately get a loop that goes down a page,
copies x number of rows in 1 column, copies to another sheet where it is
processed.

this portion:
I by hand copy back to original sheet, where I paste values to a different
column, move down x number of rows and go back to the original column, copy
same same number of rows further down the sheet. (poor mans method for
now..)
but am just between steps of have paste values / move down &
just what inferred: move to a different column copy down again.
thanks.