PDA

View Full Version : [SOLVED] Copy all text on Sheet2 and paste to the Last unused row on Sheet1



markpem
12-18-2014, 04:41 AM
Hello,

I have some text on sheet2 which I need all the data copying off it (basically the whole sheet, no headers) and then pasting into sheet1 but at the last unused row (there is already data in sheet1 and it needs to be appended at the bottom)

Could anyone please provide any code?

Thanks

Kenneth Hobs
12-18-2014, 01:52 PM
In a Module:



Sub Sheet2to1()
Dim r1 As Range, r2 As Range
Set r2 = Worksheets("Sheet2").UsedRange.Offset(1)
Set r2 = r2.Resize(r2.Rows.Count - 1)
Set r1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
r2.Copy r1
End Sub

YasserKhalil
12-18-2014, 10:24 PM
Hello Mr. Kenneth Hobs (http://www.vbaexpress.com/forum/member.php?3661-Kenneth-Hobs)
What about this ::
What's wrong with that modification?

Sub Sheet2to1()
Dim r1 As Range, r2 As Range
Set r2 = Worksheets("Sheet2").UsedRange
Set r1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
r2.Copy r1
End Sub

tecman
12-19-2014, 08:16 AM
Both versions did not work for me. The following modification does the work:


Sub Sheet2to1()
Dim r1 As Range, r2 As Range
Set r2 = Worksheets("Sheet2").UsedRange
Set r1 = Worksheets("Sheet1").UsedRange
Dim r3 As Range
Set r3 = Worksheets("Sheet1").Range("A" & r1.Rows(r1.Rows.Count).Row + 1)
r2.Copy r3
End Sub