macro problem: range offset question
 

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!!

macro problem: range offset question
 

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

"lacy" wrote:

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!!

macro problem: range offset question
 

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

"Atishoo" wrote:

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

"lacy" wrote:

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!!

macro problem: range offset question
 

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"lacy" wrote in message
...
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!!

macro problem: range offset question
 

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

"Don Guillett" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"lacy" wrote in message
...
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!!

macro problem: range offset question
 

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!

"Don Guillett" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"lacy" wrote in message
...
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!!

macro problem: range offset question
 

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

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

"Don Guillett" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"lacy" wrote in message
...
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!!