Sep 13 2022 06:43 PM
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...
Sep 13 2022 07:26 PM
Sep 14 2022 12:46 AM
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
Sep 14 2022 07:35 AM
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
Sep 14 2022 08:16 AM
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.