problem breaking macro into 2 subroutines

[surfunbear]

I created a macro by hand called Macro2, I then changed some of the
literals into paramters
and created a different macro called call_Macro2 to invoke it. The
ideas was
I created a macro to create a pivot table from a sheet, and then tried
to invoke it on multiple sheets. It fails with a 1004 run time error
one the second call to Macro2()
at the PivotCaches.Add() call. By breaking this up into two
subroutines, I am hoping
I can make it easier to modify and generate code in Perl.
When I have it all inside of one macro as at the end it seems to work
fine.



Sub Macro2(ByVal pivot_name As String, ByVal sheet_name As String,
ByVal row_specifier As String)
'
' Macro2 Macro
' Macro recorded 3/22/2006 by Laurence
'

'
MsgBox "in macro2, piv = " & pivot_name & " sheet = " & sheet_name & "
spec = " & row_specifier, vbOKOnly


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=
_
sheet_name & "!" & row_specifier).CreatePivotTable
TableDestination:="", TableName:= _
pivot_name, DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With
ActiveSheet.PivotTables(pivot_name).PivotFields("marketsegment")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables(pivot_name).AddDataField
ActiveSheet.PivotTables( _
pivot_name).PivotFields("count'"), "Sum of count'", xlSum
ActiveSheet.PivotTables(pivot_name).AddDataField
ActiveSheet.PivotTables( _
pivot_name).PivotFields("fico"), "Count of fico", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub





Sub call_Macro2()

For idx = 2 To 5
mysheet = Worksheets(idx).Name
ptab = "PivotTable" & idx
MsgBox "macro invocation to create pivot table " & ptab & " from
sheet " & mysheet, vbOKOnly
Call Macro2(ptab, mysheet, "R1C1:R1258C7")
Next idx
End Sub


========================================================
Doing something like this seems to work fine:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/22/2006 by Laurence and loaded from file
'

'
For mysheet_idx = 2 To 5
mysheet = Worksheets(mysheet_idx).Name
ptab = "PivotTable" & mysheet_idx
MsgBox "macro is on " & mysheet & ", " & ptab, vbOKOnly

' Next mysheet_idx
'Sheet1!R1C1:R1258C7

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=
_
mysheet & "!R1C1:R1258C7").CreatePivotTable
TableDestination:="", TableName:= _
ptab, DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables(ptab).PivotFields("marketsegment")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables(ptab).PivotFields("fmt_pkgcode")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables(ptab).AddDataField ActiveSheet.PivotTables(
_
ptab).PivotFields("segdesc"), "Count of segdesc", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("D10").Select
Next mysheet_idx
End Sub

 

[Tom Ogilvy]

In your code, the only thing I see you doing is changing the source of the
data - but all your pivottables are being created in the same cell on the
activesheet and the activesheet never changes, so I suspect the 2nd time
would fail as you describe.

 

[surfunbear]

The pivot table code creates a new worksheet.
I'm not sure how to get that worksheet as the current.
I found if I add a Range.("A1").Select
at the end of the called subroutines, it seems to fix the problem,
maybe that sets it as a side effect.