Aug 24 2021 12:22 PM
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.
Aug 24 2021 12:25 PM
Aug 24 2021 12:26 PM
Aug 24 2021 01:00 PM
Aug 24 2021 01:20 PM
Aug 24 2021 01:46 PM
SolutionSee 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
Aug 24 2021 01:46 PM
SolutionSee 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