Need help adjusting code to add to list instead of replace list.

Occasional Contributor

I'm still a novice when it comes to VBA Code.  

 

Can you please help adjust this code so that when it copies to Sheet 2 (Historical Data) it adds to the list instead of replacing the list?

 

Here is my code...

Screenshot 2022-09-13 175107.png

4 Replies
我的EXCEL,每次从微信上复制到桌面,后缀变成XSLM,导致我的表格都打不开了

@heathermarie923 

Can you paste your code into your reply instead of attaching the code in a screenshot? For example the code below can easily be copied and pasted into the VBA editor. The code from a screenshot has to be typed into the VBA editor manually. The code below is only sample code not a possible solution to your question.

 

Sub MyMacro()
Dim Destws As Worksheet
Dim lastrow As Long

Set Destws = Worksheets("Tabelle19")
lastrow = Destws.Cells(Destws.Rows.Count, "A").End(xlUp).Row
Destws.Range("B2:B" & lastrow).FormulaR1C1 = "=COUNTIFS(R2C1:R" & lastrow & "C1,RC[-1])"
End Sub

@Quadruple_Pawn 

 

Sorry about that.  How is this?

 

Private Sub Workbook_Open()
'Declaring variables
Dim i, Lastrow As Long
Lastrow = Sheets("List of UPCs").Range("B" & Rows.Count).End(xlUp).Row
Sheets("Historical Data").Range("A2:E128").ClearContents

For i = 2 To Lastrow
If Sheets("List of UPCs").Cells(i, "E").Value < Date Then
Sheets("List of UPCs").Cells(i, "E").EntireRow.Copy Destination:=Sheets("Historical Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
Sheets("List of UPCs").Range(Cells(i, 3), Cells(i, 5)).Clear
End If
Next i

End Sub

@heathermarie923 

Sub Workbook_Open()
'Declaring variables
Dim i, j, Lastrow As Long

j = 2
Lastrow = Sheets("List of UPCs").Range("B" & Rows.Count).End(xlUp).Row
Sheets("Historical Data").Range("A2:E128").ClearContents

For i = 2 To Lastrow
If Sheets("List of UPCs").Cells(i, "E").Value < Date Then
Sheets("List of UPCs").Cells(i, "E").EntireRow.Copy _
Destination:=Sheets("Historical Data").Cells(j, 1)
j = j + 1
Sheets("List of UPCs").Range(Cells(i, 3), Cells(i, 5)).Clear
End If
Next i

End Sub

You can try this code which seems to work in the attached file.