Basic: Copy-Paste Data using xlDown multiple times

[afzalw]

I get this code from RECORDING MACRO to copy data from one sheet to another sheet.

Data I want to copy has spaces in it so I have to use xlDown five times to completely select the data.

But when I paste this data to other sheet only data selected by first xlToRight and first xlDown is pasted.

While no data was selected with rest of the xlDown script mentioned in bold.



Sub Macro2()
ThisWorkbook.Sheets("Distress").Select
Range("C1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy


SheetName = Sheets("Part A").Range("B6").Text
Sheets(SheetName).Select

Range("O1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=True
End Sub

[Jacob Hilderbrand]

Can you start from the bottom and do an End(xlUp) to get the row?

If not you can determine the last row and then build the range using that:

Range("C1:C" & LastRow).Copy