MACRO assistance


M

MrDave

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
 
Ad

Advertisements

J

JLatham

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top