Hi - The code works great in a standalone workbook. What I'm trying to do is basically add your code to my existing code that is designed to export a number of worksheets including hardcoded data from a workbook. The pivots within the worksheets are linked to data within the workbook however I'm wanting use your code to change the data source of the pivot to look within the newly created workbook. Rather than specifying the worksheets (James Campbell) I would it follow my code in the loop. Hope that makes sense, Hope you can follow the code. thank you!!
Dim wbNew As Workbook
Dim rngTM As Range
Dim strPath As String
Dim rSourceData As Range
Dim LastRow As Long
Dim LastCol As Long
On Error GoTo Errorcatch
Application.ScreenUpdating = False
strPath = "C:\Users\pandoan\Desktop\test\"
Set rngTM = Sheets("Flow TM's").Range("A1")
Sheets(Array("hardcoded data", rngTM.Value)).Copy
Set wbNew = ActiveWorkbook
.Sheets("hardcoded data").Visible = False
Application.Goto .Sheets(1).Range("B13"), True