Macro Pivot Table ?, Should be easy

[NateUNI]

i have the following macro code to make a pivot table:

Sub YTD_SALES()
'
' YTD_SALES Macro
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"DiamondV_062002!R1C1:R1000C15&quot.CreatePivotTable TableDestination:="", _
TableName:="PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1&quot.SmallGrid = False
ActiveSheet.Select
ActiveSheet.Name = "YTD_SALES"
With ActiveSheet.PivotTables("PivotTable1&quot.PivotFields("State&quot
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1&quot.PivotFields("Name&quot
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1&quot.PivotFields("YTD Sales&quot
.Orientation = xlDataField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1&quot.PivotFields("Count of YTD Sales&quot. _
Function = xlSum
Columns("C:C&quot.Select
Selection.NumberFormat = "$#,##0.00"
Columns("A:A&quot.EntireColumn.AutoFit
Columns("B:B&quot.EntireColumn.AutoFit
Columns("C:C&quot.EntireColumn.AutoFit
End Sub

My problem is that i want to export this macro and import it on another file. However when i do this i get an error because the current file that i am trying to imput it to is trying to find the source data to make the pivit table (SourceType:=xlDatabase, SourceData:="DiamondV_062002!R1C1:R1000C15&quot. I want the file to use the current sheet of the current document. Hope that makes sense, Thanks!

 

[xlbo]

Well, either set a variable -
Dim Datasheet as worksheet
Set Datasheet = sheets("SheetName&quot
or
Set Datasheet = sheets(Sheetindex)

then amend the code to reflect this HTH
~Geoff~