Forum Discussion

Copper Contributor

Aug 24, 2021

Solved

VBA help

I have a fairly simple macro that copies a form with data in it and pastes it to the next empty row. The issue I have is that it will only clear the original cells that I cleared data from. (Recorded...

Aug 24, 2021

See if this works (may have to adjust the " - 8" and " - 16" after the lastrows to fit your data set better)

Sub New_PO()

    Dim lastRow As Long
    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    
    With ws
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        .Range("A4:P11").Copy
        .Range("A" & lastRow).PasteSpecial Paste:=xlPasteValues
        .Range("D" & lastRow - 8 & ":J" & lastRow - 16).ClearContents
        .Range("L" & lastRow - 8 & ":O" & lastRow - 16).ClearContents
    End With
        
End Sub

Mark_J_W

Copper Contributor

Aug 24, 2021

The first time I run the macro it would be the two ranges that are in the macro. Every time after the first it needs to move down by 6 rows.
What I have is a PO book that I need to easily be able to add the next section to. Each Section is 8 rows by 16 columns.

DKoontz

Steel Contributor

Aug 24, 2021

Mark_J_W

See if this works (may have to adjust the " - 8" and " - 16" after the lastrows to fit your data set better)

Sub New_PO()

    Dim lastRow As Long
    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    
    With ws
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        .Range("A4:P11").Copy
        .Range("A" & lastRow).PasteSpecial Paste:=xlPasteValues
        .Range("D" & lastRow - 8 & ":J" & lastRow - 16).ClearContents
        .Range("L" & lastRow - 8 & ":O" & lastRow - 16).ClearContents
    End With
        
End Sub

Mark_J_W
Copper Contributor
Aug 24, 2021
Thank you I have it going now!