Paste Data Below last row

[karl_learnvba]

I have data in 1 row that I would like ot create a table from.
The final table will be approx 9 cells wide.
If I were cutting and pasting the data manually I would :-
1) Select all items in the row from active cell to the far right (ativecell is L8 in the attached screenshot)
2) Cut
3) Paste below previous data

I got this far before running out of ideas I am seing an error in the pasting function towards the end
Can somebody please help ? Thanks in advance !

Sub Macro1()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

' Cut & paste the Data
Dim cutRange As Range
Set cutRange = ws.Range(ActiveCell, ActiveCell.End(xlToRight))
cutRange.Cut
ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

End Sub

 

[rlv01]

Try this.

Sub Macro1()
    Dim ws As Worksheet
    Dim LastRow As Long
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Cut & paste the Data
    Dim cutRange As Range
    With ws
        Set cutRange = .Range(ActiveCell, .Cells(ActiveCell.Row, .Columns.Count).End(xlToLeft))
        LastRow = .Cells(.Rows.Count, ActiveCell.Column).End(xlUp).Row
        cutRange.Cut .Cells(LastRow + 1, ActiveCell.Column)
    End With
End Sub

 

[karl_learnvba]

Try this.

Sub Macro1()
    Dim ws As Worksheet
    Dim LastRow As Long
    Set ws = ThisWorkbook.Worksheets("Sheet1")
   
    ' Cut & paste the Data
    Dim cutRange As Range
    With ws
        Set cutRange = .Range(ActiveCell, .Cells(ActiveCell.Row, .Columns.Count).End(xlToLeft))
        LastRow = .Cells(.Rows.Count, ActiveCell.Column).End(xlUp).Row
        cutRange.Cut .Cells(LastRow + 1, ActiveCell.Column)
    End With
End Sub

Hi rlv01 - Thank you so much for spending time and sending this over.

An additional question:-

The procedure as above does select all the data, and it pastes it 1 row down.
What would I need to change to ensure that it pastes the data into the next empty row bottom of column A (Pic attached)

Thankyou!

 

[rlv01]

The procedure as above does select all the data, and it pastes it 1 row down.
What would I need to change to ensure that it pastes the data into the next empty row bottom of column A (Pic attached)

Just to be clear, "Paste below the previous data" means to me that that if the active cell is L8, then the data gets pasted to L9. Changing the paste location to column A is easy enough:

Sub Macro1()
    Dim ws As Worksheet
    Dim LastRow As Long
    Set ws = ThisWorkbook.Worksheets("Sheet1")
  
    ' Cut & paste the Data
    Dim cutRange As Range
    With ws
        Set cutRange = .Range(ActiveCell, .Cells(ActiveCell.Row, .Columns.Count).End(xlToLeft))
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        cutRange.Cut .Cells(LastRow + 1, 1)
    End With
End Sub

But that also has the potential to seriously mess up your column organization depending on where the ActiveCell is at the time you run the macro.