Newryvidoman
03-07-2021, 04:00 AM
Can anybody please show me how to loop the following macro until a cell is empty in column A of Sheet("Data Sheet Copy"), thanks
The macro code below selects & copies the first rows of data (including the header) in Sheet("Data Sheet Copy") TransposePaste in an empty row of column A in Sheet("New Sheet"). I then select Sheet("Data Sheet Copy") and delete Row 2 (the row that has been copied). I have tried several code snippets but have been unsuccessful in looping this macro until a cell is empty in column A of Sheet("Data Sheet Copy")
Sub Copy_Row2_TransposePaste_Delete_Row2()
Sheets("Data Sheet Copy").Select
Range("A1:A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("NewSheet").Select
With Sheets("NewSheet")
lst = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & lst).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End With
Sheets("Data Sheet Copy").Select
Application.CutCopyMode = False
Rows("2:2").Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(1, 0).Select
End Sub
The macro code below selects & copies the first rows of data (including the header) in Sheet("Data Sheet Copy") TransposePaste in an empty row of column A in Sheet("New Sheet"). I then select Sheet("Data Sheet Copy") and delete Row 2 (the row that has been copied). I have tried several code snippets but have been unsuccessful in looping this macro until a cell is empty in column A of Sheet("Data Sheet Copy")
Sub Copy_Row2_TransposePaste_Delete_Row2()
Sheets("Data Sheet Copy").Select
Range("A1:A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("NewSheet").Select
With Sheets("NewSheet")
lst = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & lst).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End With
Sheets("Data Sheet Copy").Select
Application.CutCopyMode = False
Rows("2:2").Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(1, 0).Select
End Sub