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

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.

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!