s.schwantes
01-19-2015, 05:13 PM
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
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