macro problem: range offset question

L

lacy

Dim myRange As Range

ActiveWorkbook.Sheets("hello").Select
Range("L2").Select
myRange = Range(Selection, Selection.End(xlDown)).Select
myRange2 = myRange.Offset(0, 11)
myRange2.Select
Selection.Copy
Sheets("solong").Select
Range("b101000").Select
Selection.End(xlUp).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

the problem is that the macro is falling over with the range in column L
selected. the offset selection is not made. is there a problem with the
sytax? thanks!!
 
A

Atishoo

Am confused, as you have already predifined my range as being l2 down why not
predifine myrange2 as W2 down?
 
L

lacy

b/c the w column has a much long list. so if i end down while selected on w2
, i'll select a range that is too long. i only want to select the range in w
that corresponds the rows in L. thanks
 
D

Don Guillett

I think?? this is what you may want. Notice the DOTS and their placement.

sub copycolW()
With Sheets("hello")
.Range(.Range("l2"), .Range("l2").End(xlDown)).Offset(, 11).Copy
Sheets("solong").Cells(Rows.Count, "b").End(xlUp).Offset(1) _
.PasteSpecial Paste:=xlPasteValues
End With
end sub
 
A

Atishoo

Or if your insistant on selecting the ranges then perhaps you need to set
your variable ranges? Might be wrong this not my bag!
Dim myRange As Range
Dim myRange2 As Range
ActiveWorkbook.Sheets("hello").Select
Range("L2").Select
Set myRange = Range("L2").End(xlDown)
myRange.Select
Set myRange2 = myRange.Offset(0, 11)
myRange2.Select
Selection.Copy
Sheets("solong").Select
Range("b101000").Select
Selection.End(xlUp).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
 
A

Atishoo

Don you prompted me recently about dots and their placement! made me go off
and find out how important one tiny little dot can be! Dont leave specks of
dust on your monitor it can be very confusing!
 
L

lacy

i think i'm good. did something simpler. thanks!

Range(Range("L2"), Range("L2").End(xlDown)).Offset(0,1).Copy
 

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

Similar Threads


Top