vba to change pivot source loop

[p4nny]

Hi, I have the following which is designed to export sheets from a workbook along with raw data (hardcoded data) and save them.. What I would like is to change the pivot source in each of the newly exported sheets...

Ideally it would change the source prior to saving..

I would like the new source to be range (DZ1:ER) of the sheet called Hardcoded data. Pivottable name is PivotTable6 for each pivot..

Rng Tm contains the names of the tabs to be exported.

Hope someone can help. Thanks in advance.


Sub exportsheets()

Dim wbNew As Workbook
Dim rngTM As Range
Dim strPath As String




On Error GoTo Errorcatch


Application.ScreenUpdating = False


strPath = "C:\Users\pandoan\Desktop\test\"
Set rngTM = Sheets("Flow TM's").Range("A1")


Do
Sheets(Array("hardcoded data", rngTM.Value)).Copy
Set wbNew = ActiveWorkbook
With wbNew
.Sheets("hardcoded data").Visible = False
Application.Goto .Sheets(1).Range("B13"), True

Range("F5:G5", "T3:U3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Range("B2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

wbNew.SaveAs strPath & rngTM & Format(Date, "ddmmmyyyy") & ".xlsm", FileFormat:=52
ActiveWorkbook.Close

End With
Set rngTM = rngTM.Offset(1, 0)
Loop Until IsEmpty(ActiveCell)

Application.ScreenUpdating = True

Errorcatch:
MsgBox Err.Description




End sub