VBA help

[Mark_J_W]

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 Macro)

For instance if I run the macro it copies cells A1:E5 and then pastes those to A6:E10, then it clears the newly copied cells. If I run the macro a second time it pastes the data from A1:E5 to A11:E15 and clears the data from cells A6:E10.

[#2]

Could you post your macro? Sounds like an issue with using active/select in the macro recorder.

[#3]

Sub New_PO()
'
' New_PO Macro
'

'
Range("A4:P11").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=6
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste
Range("D36:J43").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("L36:O43").Select
Selection.ClearContents
Range("D36").Select
End Sub

[#4]

Which cells are you trying to clear? The macro recorded is using hard coded references so it's not updating to factor in the extra pasted data.

[#5]

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.

[#6 · Accepted solution]

@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

[#7]

Thank you I have it going now!