universal code
 

I used the record macro so I can programatically re-run getting external data
into a pivot table after clearing and deleting all the cells. The macro
recorded explicitly to the workbook I was working in while recording. I'd
like help with making my code more universal. How should I modify the
following code to allow it to run on different workbooks?

..CreatePivotTable TableDestination:=" ' [Workbook Name.xls] Query Name ' !
R5C1", _
TableName:="PivotTable5", DefaultVersion:=xlPivotTableVersion10

and

ActiveWindow.Visible = False
Windows("Workbook Name.xls").Activate
Range ("A2").Select

Thanks,
Lou Sanderson

universal code
 

up front

Set oWb = workbooks("Workbook Name.xls")


and then

..CreatePivotTable TableDestination:=" ' [" oWb.Name & "] Query Name ' !
R5C1", _
TableName:="PivotTable5", DefaultVersion:=xlPivotTableVersion10

and

ActiveWindow.Visible = False
Windows(oWb.Name).Activate
Range ("A2").Select



--

HTH

RP
(remove nothere from the email address if mailing direct)


"lou sanderson" wrote in message
...
I used the record macro so I can programatically re-run getting external
data
into a pivot table after clearing and deleting all the cells. The macro
recorded explicitly to the workbook I was working in while recording. I'd
like help with making my code more universal. How should I modify the
following code to allow it to run on different workbooks?

.CreatePivotTable TableDestination:=" ' [Workbook Name.xls] Query Name ' !
R5C1", _
TableName:="PivotTable5", DefaultVersion:=xlPivotTableVersion10

and

ActiveWindow.Visible = False
Windows("Workbook Name.xls").Activate
Range ("A2").Select

Thanks,
Lou Sanderson

universal code
 

..CreatePivotTable

alone means nothing, there's probably a "with" few lines before,
refering to which object we add a pivot table

then, you can change it, example...

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
' stuff
.CreatePivotTable
'stuff
end with


this ActiveWorkbook means... the active workbook, so not one book but
anyone one (the active one)...