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.
"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
