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!!
ub exportsheets()
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")
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
With Worksheets("hardcoded data")
LastRow = .Cells(.Rows.Count, "DZ").End(xlUp).Row
Set rSourceData = .Range("DZ1:ER" & LastRow)
End With
With Worksheets("Matt Wilkins").PivotTables("PivotTable6")
.ChangePivotCache ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=rSourceData)
End With
ActiveWorkbook.Save
ActiveWorkbook.Close
End With
Set rngTM = rngTM.Offset(1, 0)
Loop Until IsEmpty(ActiveCell)
Application.ScreenUpdating = True
Errorcatch:
MsgBox Err.Description
End Sub
