Hi! I'm trying to modify an existing macro... and am running into problems.
I want to copy a range to a new sheet, however when we get to the new sheet, I want to check first if an entry with the same Unique Identifier already exists. If YES, then copy over it. If NO, then copy into the next available row.
I can't seem to get my code to stop looping.... not sure why.. it seem to has something to do with the ActiveCell.. I thought that was on the History sheet but I'm suspecting its not..
Sheet with info to copy = PR sheet
AuditNum has unique identifier in AJ4 of PR sheet
Destination Sheet = History sheet
Where to start checking for duplicates = Between A4 and last row
Thank you in advance for your great advice! I always learn so much!
I want to copy a range to a new sheet, however when we get to the new sheet, I want to check first if an entry with the same Unique Identifier already exists. If YES, then copy over it. If NO, then copy into the next available row.
I can't seem to get my code to stop looping.... not sure why.. it seem to has something to do with the ActiveCell.. I thought that was on the History sheet but I'm suspecting its not..
Sheet with info to copy = PR sheet
AuditNum has unique identifier in AJ4 of PR sheet
Destination Sheet = History sheet
Where to start checking for duplicates = Between A4 and last row
Thank you in advance for your great advice! I always learn so much!
Code:
Sub CopyPRData()
'Copies values from PR form to a History Tab so history of edits will not be lost
Dim PRLastCell As String 'on PRHistory Sheet
Dim PRTrackingType As String 'to match the AuditNum from PRSheet
Dim PRAuditNum As String 'From PR Sheet
PRLastCell = Sheets("PRHistory").Range("A655").End(xlUp).Address
Sheets("PR").Activate
Range("AJ4").Select
Application.ScreenUpdating = False
Do Until ActiveCell.Address = PRLastCell
PRTrackingType = ActiveCell.Value
PRAuditNum = Sheets("PR").Range("AJ4").Value
Range("AJ4:BS4").Copy
Sheets("PRHistory").Activate
'Check for duplicate audit number
'Range("A4").Select
Do Until ActiveCell.Value = PRAuditNum Or ActiveCell.Value = ""
ActiveCell.Select
Loop
ActiveCell.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("A1").Select
Loop
Application.ScreenUpdating = True
End Sub