afzalw
04-15-2013, 06:03 PM
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
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