Copy and paste only unique rows from one sheet to another after refreshing data.

Machl22

New Member
Joined
Jun 16, 2018
Messages
11
Good Afternoon, All. I have a workbook, CallLog, containing the following tabs--"Summary,"Data","MysteryCallLog2," and "Pivot." "Pivot" is merely derived from "Data," and used to visualize data in graphs and slicers on "Summary."

"Data" contains the resulting information from a SharePoint survey, and whose data is in a table and populates charts and graphs on "Summary." This survey has reached the maximum number of responses, and thus I've disconnected "Data" from the SharePoint Survey, and the new "MysteryCallLog2" tab contains the same survey questions and data. I essentially want to be able to move new records or responses from "MysteryCallLog2" to "Data" without copying all of the records from "MysteryCallLog2" each time so I have one singular sheet "Data" from which the charts are populated. Using my current code, and "Refresh Data" button on "Summary," unfortunately all of the records on "MysteryCallLog2" are copied each time and thus there are duplicate records.

The goal would be to after refreshing data on "MysteryCallLog2," and pulling down new survey responses on that tab, to copy only those new records from "MysteryCallLog2" to "Data" after the last row. Column A contains respondent's name; Column F contains Date/Time "mm/dd/yyyy hh:mm (24 hour format)", and Column I contains Office "District: Office ; Phone #."

Any assistance is greatly appreciated.

VBA Code:
Sub RefreshData()
' Refresh_Data Macro
    Application.ScreenUpdating = False
Workbooks(ThisWorkbook.Name).RefreshAll
'Make length of range dynamic with new entries and then copy
With Worksheets("MysteryCallLog2")
    Dim xRow As Integer
    xRow = .Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:X" & xRow).AdvancedFilter Action:=xlFilterCopy,_ Copy
'End With

Rows(MysteryCallLog2.Range("A3").End(xlUp).Row).Copy Data.Range("A3").End(xlUp)(2)


'Copy data
Worksheets("MysteryCallLog2").Range("A2:X" & xRow).Copy

'Paste to end of records in "Data" tab
With Sheets("Data")
    .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).RemoveDuplicate Columns:=1,6,9, Header:=xlNo. PasteSpecial(xlPasteValues)
End With

'Suppress clipboard popup message
Application.CutCopyMode = False

    Sheets("MysteryCallLog2").Visible = False
    Sheets("Pivot").Visible = True
    Sheets("Pivot").Select
    ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    ActiveSheet.PivotTables("PivotTable12").PivotCache.Refresh
    ActiveSheet.PivotTables("PivotTable10").PivotCache.Refresh
    ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh
    Sheets("Pivot").Visible = False
    
    Sheets("Summary").Select
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Watch MrExcel Video

Forum statistics

Threads
1,118,309
Messages
5,571,468
Members
412,395
Latest member
nielsvanlit
Top