problem breaking macro into 2 subroutines

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

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.

--
Regards,
Tom Ogilvy


"[email protected]" wrote:

>
> 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
>
>