variable not defined compile error

[sschwant]

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

 

[shg]

Looking only at that, you're missing a colon:

Selection.Copy Destination[B][COLOR="#FF0000"][SIZE=3]:[/SIZE][/COLOR][/B]=rng1

 

[MARK858]

Selection.Copy Destination = rng1

should probably be

Selection.Copy Destination[COLOR="#FF0000"]:=[/COLOR]rng1

Edit: too slow at typing as beaten by Shg. Moderator can delete this post if read.

 

[sschwant]

Please don't delete this post yet.

I added the colon, but the sub is still not finished (complete). This works fine, but only if you want to paste the range as columns. I need to paste the range as a transposed range; from columns to rows.


Now I'm getting this error: "Select method of Range class failed"

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
End Sub

Please advise, thanks!!!

Steve

 

[AlphaFrog]

Try this...

[COLOR=darkblue]Sub[/COLOR] test()
    [COLOR=green]'Copy[/COLOR]
    Range("Extract_Fields1").Offset(0, 1).Copy
    
    [COLOR=green]'Paste[/COLOR]
    ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial _
        Paste:=xlPasteAll, Transpose:=[COLOR=darkblue]True[/COLOR]
        
    Application.CutCopyMode = [COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[sschwant]

Try this...

[COLOR=darkblue]Sub[/COLOR] test()
    [COLOR=green]'Copy[/COLOR]
    Range("Extract_Fields1").Offset(0, 1).Copy
    
    [COLOR=green]'Paste[/COLOR]
    ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial _
        Paste:=xlPasteAll, Transpose:=[COLOR=darkblue]True[/COLOR]
        
    Application.CutCopyMode = [COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

AlphaFrog -

You're a life saver! Thanks, that works perfectly!

Cheers,

Steve