Making a Pivot Table from data of a Close sheet visa Button(Macro)

[Nano]

Hello

I have two sheets
(1) Sheet A has the the data
(2) Sheet B has a Button (w/ macro) that gets the data from Sheet A
pivoted. (Make Pivot Table of Data from Sheet A)

Working:
The button on Sheet B works fine when Sheet A is open and pivot the
data the way I want.

Issue:
But when sheet A is closed is closed, it gives the Error reading

"Cannot Open Pivot Table Source File <filename>"

[Exact Error: http://img820.imageshack.us/i/image001x.png/]

Can I somehow edit the macro in a way that it can Pivot the data even
when Sheet A is closed.

The VBA code for marco is given below.

Thanks & Regards,
- Nano

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
VBA Code:


Sub GetDataOOW()
'
' GetDataOOW Macro
' Macro recorded 8/2/2010 by MHR
'

'
Range("A7").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
"'[Open-Orders.XLS]Open-Orders'!
R1C2:R2036C12").CreatePivotTable _
TableDestination:= _
"'[Clover sales and SOH 3PL1 - 3PL2 (TEMPLATE).xls]Open Orders
Work'!R7C1", _
TableName:="PivotTable8",
DefaultVersion:=xlPivotTableVersion10
With
ActiveSheet.PivotTables("PivotTable8").PivotFields("Material")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable8").AddDataField
ActiveSheet.PivotTables( _
"PivotTable8").PivotFields(" Open Qty."), "Count of Open
Qty.", xlCount
ActiveSheet.PivotTables("PivotTable8").PivotFields("Count of Open
Qty."). _
Function = xlSum
End Sub