Pasting issue with VBA (Simple)

[bran8989]

Looking for help on this, I'm erroring out on my pasting line...

Sub TEST()


i = 1
n = 2
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row


For n = 2 To lastrow


With ActiveSheet
.Range("A" & n, Range("A" & n).End(xlToRight)).Cut
.Range("A" & n).End(xlToRight).Paste
End With


Next n


End Sub

 

[JLGWhiz]

With ActiveSheet
.Range("A" & n, Range("A" & n).End(xlToRight)).Cut .Range("A" & n).End(xlToRight)
End With

Try this, do you want to cut and paste to the same location?
Actually, this will probably error, because there is insufficient room to paste if there is no other data to the rigtht.

 

[JackDanIce]

If your range is A2:D2, you're cutting and placing it into D2:G2?
Try:

Sub Test()

    Dim LR  As Long
    Dim x   As Long
    Dim y   As Long
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        For x = 2 To LR
            y = .Cells(x, .Columns.Count).End(xlToLeft).Column
            .Cells(x, y).Resize(, y).Value = .Cells(x, 1).Resize(, y).Value
            .Cells(x, 1).Resize(, y - 1).ClearContents
        Next x
    End With
    
    Application.ScreenUpdating = True

End Sub

 

[bran8989]

If your range is A2:D2, you're cutting and placing it into D2:G2?
Try:

Sub Test()

    Dim LR  As Long
    Dim x   As Long
    Dim y   As Long
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        For x = 2 To LR
            y = .Cells(x, .Columns.Count).End(xlToLeft).Column
            .Cells(x, y).Resize(, y).Value = .Cells(x, 1).Resize(, y).Value
            .Cells(x, 1).Resize(, y - 1).ClearContents
        Next x
    End With
    
    Application.ScreenUpdating = True

End Sub

Both of these work thanks!!!

 

[JackDanIce]

You're welcome