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 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.

Macros and VBA

office 365

DKoontz

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

6 Replies

DKoontz
Steel Contributor
Aug 24, 2021
Could you post your macro? Sounds like an issue with using active/select in the macro recorder.
- Mark_J_W
  Copper Contributor
  Aug 24, 2021
  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
  - DKoontz
    Steel Contributor
    Aug 24, 2021
    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.

Forum Discussion

VBA help