variable not defined compile error

[s.schwantes]

I have a range that I need to copy from on ws to another. I have some code that finds the end of the rows on the second ws to paste the copied range into ...

That was working, but I had to tinker with it in order to be able to Transpose the copied range into the other ws.

Here's the original code which works:


Sub findbottom_paste()
Dim rng1 As Range
Set rng1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
Worksheets("Sheet2").Range("A1:J10").Copy _
Destination:=rng1
End Sub

And then my attempt to implement the copy paste transpose, which results in the error on this posts' title, which happens on this line: Selection.Copy Destination = rng1


Sub test()
Dim rng1 As Range
Set rng1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
Application.Goto Reference:="Extract_Fields1"
Selection.Offset(0, 1).Select
Selection.Copy Destination = rng1
'Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
'Sheets("Export").Select
'Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub


Thanks in advance for any ideas on how to fix this problem!

Steve

cross posted on mrexcel.com:
http://www.mrexcel.com/forum/excel-questions/830009-variable-not-defined-compile-error.html#post4048892

[mancubus]

hey!

the solution posted on mrexcel forums reminds us that we dont need to select objects ("range" in your case) in order to work with them. :)

[snb]

instead of


Sub findbottom_paste()
Dim rng1 As Range
Set rng1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
Worksheets("Sheet2").Range("A1:J10").Copy _
Destination:=rng1
End Sub

use

Sub M_snb()
sheets("Sheet2").Range("A1:J10").Copy ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1)
End Sub
or

Sub M_snb()
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1).resize(10,10)=sheets("Sheet2").Range("A1:J10").Value
End Sub

[SamT]

Syntax error:

Destination = rng1
Should bead

Destination:=rng1