VBA Copy & Paste Rows into different sheet without duplicates

[sylvanb]

Hi,
I am very new to VBA and am having trouble finding a way to navigate this problem.

For context, I am changing a database of students from access into excel. I have a master sheet with all the students on, and then smaller sheets with which member of my team is responsible for them. Unfortunately due to Data Protection Rights I can't share any screenshots or mini-sheets.

The problem I am having is finding code that will copy entire rows based off of one columns data, but then won't duplicate the data in the sheet where it is pasted. For example, in column "CG" of the master sheet is my team members surname. I have found code that will filter by this and copy and paste to their relevant sheet, but if I run it again I end up with two copies in the destination sheet. This is an issue as we are constantly adding new students, and I want to avoid manually copying data across every time. Is there any code that will filter column "CG" by surname, copy the relevant rows, and paste it ONLY if it is a new addition? This way I could leave it to run automatically and the sheets would auto fill.

I don't know how easy this would be as well but if it could be a paste link that would be ideal...

Any help or words of advice would be much appreciated!

Thanks

 

[fadee2]

as we are constantly adding new students,

are these students being added at the end of your master sheet, whenever you add them?

 

[sylvanb]

They are added alphabetically, but I guess they could just be added at the end and then sorted alphabetically if that makes it easier

 

[fadee2]

The reason I asked this was that, there could be 2 ways you can achieve your task. If new data is added at the end of your master sheet every time and no sorting is applied then try following code.

Sub abc()
Dim t As Long

t = Sheets("sheet1").Range("s1").Value
If t = 0 Then t = 2

lr = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row

With Sheets("sheet1")
    .Range("A1:N" & lr).AutoFilter field:=2, Criteria1:=Sheets("sheet1").Range("s2")
    On Error Resume Next
        If t = lr Then
            MsgBox "Data not updated."
            Sheets("sheet1").ShowAllData
            Sheets("sheet1").Range("s1") = lr
            Exit Sub
        End If
   
    .Range("a" & t & ":n" & lr).SpecialCells(xlCellTypeVisible).Copy
   
    If t = lr Then
        MsgBox "Data not updated."
        Sheets("sheet1").ShowAllData
        Sheets("sheet1").Range("s1") = lr
        Exit Sub
    End If
   
    If .Range("a" & t & ":n" & lr).SpecialCells(xlCellTypeVisible) Is Nothing Then
        MsgBox "Data with provided criteria not found."
        Sheets("sheet1").ShowAllData
        Sheets("sheet1").Range("s1") = lr
        Exit Sub
    End If

End With

With Sheets("sheet2")
    .Activate
    lr2 = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Range("a" & lr2 + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End With


Sheets("sheet1").ShowAllData
Sheets("sheet1").Range("s1") = lr

lr = ""
lr2 = ""
End Sub

The code uses your last row of data as the checkpoint, meaning, every time the code is executed it keeps a track of last row of data in sheet1 at cell S1, while using the criteria available @ cell S2 to filter the master data, and copy paste the data to sheet2. The check point ensures that non of the previous data is used while performing the copy paste operation. But again this is only possible if the data is being added at the very end of master sheet.

There are a couple of logical error handlers that check for errors like after applying the filter no data is available for provided criteria and exiting the code if new data is not available for processing, as well.

I used a sample data since you did not provide actual data to work with. you can change cell references to best suite your needs.
the sample workbook is available here.

hth....

 

[sylvanb]

The reason I asked this was that, there could be 2 ways you can achieve your task. If new data is added at the end of your master sheet every time and no sorting is applied then try following code.

Sub abc()
Dim t As Long

t = Sheets("sheet1").Range("s1").Value
If t = 0 Then t = 2

lr = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row

With Sheets("sheet1")
    .Range("A1:N" & lr).AutoFilter field:=2, Criteria1:=Sheets("sheet1").Range("s2")
    On Error Resume Next
        If t = lr Then
            MsgBox "Data not updated."
            Sheets("sheet1").ShowAllData
            Sheets("sheet1").Range("s1") = lr
            Exit Sub
        End If
  
    .Range("a" & t & ":n" & lr).SpecialCells(xlCellTypeVisible).Copy
  
    If t = lr Then
        MsgBox "Data not updated."
        Sheets("sheet1").ShowAllData
        Sheets("sheet1").Range("s1") = lr
        Exit Sub
    End If
  
    If .Range("a" & t & ":n" & lr).SpecialCells(xlCellTypeVisible) Is Nothing Then
        MsgBox "Data with provided criteria not found."
        Sheets("sheet1").ShowAllData
        Sheets("sheet1").Range("s1") = lr
        Exit Sub
    End If

End With

With Sheets("sheet2")
    .Activate
    lr2 = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Range("a" & lr2 + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End With


Sheets("sheet1").ShowAllData
Sheets("sheet1").Range("s1") = lr

lr = ""
lr2 = ""
End Sub

The code uses your last row of data as the checkpoint, meaning, every time the code is executed it keeps a track of last row of data in sheet1 at cell S1, while using the criteria available @ cell S2 to filter the master data, and copy paste the data to sheet2. The check point ensures that non of the previous data is used while performing the copy paste operation. But again this is only possible if the data is being added at the very end of master sheet.

There are a couple of logical error handlers that check for errors like after applying the filter no data is available for provided criteria and exiting the code if new data is not available for processing, as well.

I used a sample data since you did not provide actual data to work with. you can change cell references to best suite your needs.
the sample workbook is available here.

hth....

Hey sorry it took me so long to reply, thats amazing.

Thanks so much for this!

 

[fadee2]

You are welcome.... glad to help...