Macro question, Copy-Paste
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 |
Macro question, Copy-Paste
Replace "Me" with "ActiveSheet", since you are calling the macro via keyboard
shortcuts, and aren't changing worksheets. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "MrDave" wrote: 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 |
Macro question, Copy-Paste
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. -- HTH... Jim Thomlinson "MrDave" wrote: 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 |
Macro question, Copy-Paste
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" wrote: 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. -- HTH... Jim Thomlinson "MrDave" wrote: 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 |
Macro question, Copy-Paste
Perhaps you could just tell us what you are trying to do... From just reading
your code it is a bit hard to tell. -- HTH... Jim Thomlinson "MrDave" wrote: 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" wrote: 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. -- HTH... Jim Thomlinson "MrDave" wrote: 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 |
Macro question, Copy-Paste
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. "Jim Thomlinson" wrote: Perhaps you could just tell us what you are trying to do... From just reading your code it is a bit hard to tell. -- HTH... Jim Thomlinson "MrDave" wrote: 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" wrote: 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. -- HTH... Jim Thomlinson "MrDave" wrote: 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 |
All times are GMT +1. The time now is 02:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com