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-q...ml#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