Forum Discussion
Need help adjusting code to add to list instead of replace list.
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? Her...
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
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 SubYou can try this code which seems to work in the attached file.