Replace Piv Table Data Source

I have a workbook that has a pivtable report based on the data from sheet 2. I want to use the following code or something similar to update the data on Sheet 2 of my workbook. The line "ThisWorkbook.Sheets(2).UsedRange.Offset(1).ClearContents" is upsetting to my pivtable.

How can I use VBA to completely replace the data source while maintaining the integrity of my pivtable?

Public Sub Import1_Click()
' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
Application.ScreenUpdating = False

'Clear contents of target worksheet leaving header row intact
ThisWorkbook.Sheets(2).UsedRange.Offset(1).ClearContents


' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook

' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)
Set customerWorkbook = Application.Workbooks.Open(customerFilename)


' copy data from customer to target (this) workbook, set wich worksheet and cell to insert imported data
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(2)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)
Intersect(sourceSheet.UsedRange, sourceSheet.UsedRange.Offset(1, 0)).Copy targetSheet.Range("A1")


' Close customer workbook
customerWorkbook.Close
Application.ScreenUpdating = True
MsgBox "Service Call Data Successfully Updated!"
End Sub

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)