I am an absolute novice at VBA but I need to figure it out at least a bit for a work project.
We have a spreadsheet that we use to log our trailer sales. We created another spreadsheet to keep track of the license plates and the cancellations.
The first macro needs to pull only the rows that have ME plates to the "Maine Plates" tab. Once on that tab, we will add a "Y" into column G to indicate that the registration was pulled.
The second macro will pull the rows that have a "Y" in column G.
My Macros work but I end up with duplicate information. Is there a way to only have new entries copied?
Module 1
Sub Update()
a = Worksheets("Sold Units").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a
If Worksheets("Sold Units").Cells(i, 5).Value = "ME" Then
Worksheets("Sold Units").Rows(i).Copy
Worksheets("Maine Plates").Activate
b = Worksheets("Maine Plates").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Maine Plates").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Sold Units").Activate
End If
Next
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Sold Units").Cells(1, 1).Select
End Sub
Module 2
Sub Cancellations()
a = Worksheets("Maine Plates").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a
If Worksheets("Maine Plates").Cells(i, 7).Value = "Y" Then
Worksheets("Maine Plates").Rows(i).Copy
Worksheets("Reg Cancelled").Activate
b = Worksheets("Reg Cancelled").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Reg Cancelled").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Maine Plates").Activate
End If
Next
Application.CutCopyMode = False
End Sub
We have a spreadsheet that we use to log our trailer sales. We created another spreadsheet to keep track of the license plates and the cancellations.
The first macro needs to pull only the rows that have ME plates to the "Maine Plates" tab. Once on that tab, we will add a "Y" into column G to indicate that the registration was pulled.
The second macro will pull the rows that have a "Y" in column G.
My Macros work but I end up with duplicate information. Is there a way to only have new entries copied?
Module 1
Sub Update()
a = Worksheets("Sold Units").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a
If Worksheets("Sold Units").Cells(i, 5).Value = "ME" Then
Worksheets("Sold Units").Rows(i).Copy
Worksheets("Maine Plates").Activate
b = Worksheets("Maine Plates").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Maine Plates").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Sold Units").Activate
End If
Next
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Sold Units").Cells(1, 1).Select
End Sub
Module 2
Sub Cancellations()
a = Worksheets("Maine Plates").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a
If Worksheets("Maine Plates").Cells(i, 7).Value = "Y" Then
Worksheets("Maine Plates").Rows(i).Copy
Worksheets("Reg Cancelled").Activate
b = Worksheets("Reg Cancelled").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Reg Cancelled").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Maine Plates").Activate
End If
Next
Application.CutCopyMode = False
End Sub