Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MACRO assistance
hi, my code doesn't compile, got that, that why posting. (not sure if have
terminology correct, please excuse). I'm in a module where you can apply keyboard shortcuts, with nothing but Subs() I have macro that lets me paste values in a column, moves down to start of next row after rows pasted (what you see in example below)... When done, would like to have change columns by a named range. don't think offset will work well as columns may change position. can that be done, change column with named range, in a module? works in sheet macros, such as: If Not Intersect(Me.Range(J3), .Cells) Is Nothing Then 'all With Me.Cells(.Row, J2).Select '.Offset(0, 0).Select End With End If WHAT WORKING ON: (in module1) Sub Copy() 'alt-, (comma) wip: copy column 190 rows down from active cell, pending 'reduce all windows, restore main window, pause conflict with ie, etc. click button does not work Range(ActiveCell, ActiveCell.Offset(190, 0)).Copy End Sub Sub Paste1() 'alt-. (period) works: paste values to syma ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End Sub Sub Paste2() 'alt-/ (slash) Dim M2 As String 'if can dim/ range in a module? M2 = Range("M2") Dim N3 As String N3 = Range("N3") Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=190 Worksheet(.Row, N3).Select 'invalid or unqualified reference NEED TO FIX THIS LINE, THANKS 'Range(ActiveCell, ActiveCell.Offset(190, 0)).Copy End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
MACRO assistance
Yes, you can change to a named range:
Range("NameOfRange").Select I see a line of code marked for repair: Worksheet(.Row, N3).Select 'invalid or unqualified reference NEED TO FIX THIS LINE, THANKS Worksheet needs an index for the sheet (number or name) and then reference to the range to select. Something more like Worksheet("Sheet1").Range("X4").Select or for the currently active sheet ActiveSheet.Range("X4").Select or, if you want to use row and column references, then use Cells like ActiveSheet.Cells(rowNum, colNum).Select I noted a question "'if can dim/ range in a module?" and the answer is Yes; you can pretty much declare any type of variable or constant inside of a module, but you cannot declare them Public within a module. Hope this helps. "MrDave" wrote: hi, my code doesn't compile, got that, that why posting. (not sure if have terminology correct, please excuse). I'm in a module where you can apply keyboard shortcuts, with nothing but Subs() I have macro that lets me paste values in a column, moves down to start of next row after rows pasted (what you see in example below)... When done, would like to have change columns by a named range. don't think offset will work well as columns may change position. can that be done, change column with named range, in a module? works in sheet macros, such as: If Not Intersect(Me.Range(J3), .Cells) Is Nothing Then 'all With Me.Cells(.Row, J2).Select '.Offset(0, 0).Select End With End If WHAT WORKING ON: (in module1) Sub Copy() 'alt-, (comma) wip: copy column 190 rows down from active cell, pending 'reduce all windows, restore main window, pause conflict with ie, etc. click button does not work Range(ActiveCell, ActiveCell.Offset(190, 0)).Copy End Sub Sub Paste1() 'alt-. (period) works: paste values to syma ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End Sub Sub Paste2() 'alt-/ (slash) Dim M2 As String 'if can dim/ range in a module? M2 = Range("M2") Dim N3 As String N3 = Range("N3") Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=190 Worksheet(.Row, N3).Select 'invalid or unqualified reference NEED TO FIX THIS LINE, THANKS 'Range(ActiveCell, ActiveCell.Offset(190, 0)).Copy End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need assistance | Excel Worksheet Functions | |||
Macro assistance | Excel Worksheet Functions | |||
string assistance to open workbook via macro | Excel Discussion (Misc queries) | |||
Need assistance with printing macro | Excel Worksheet Functions | |||
Assistance please? | Excel Worksheet Functions |