Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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:=xlDatab ase, 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("m arketsegment") .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:=xlDatab ase, 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("markets egment") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables(ptab).PivotFields("fmt_pkg code") .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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem breaking macro into 2 subroutines
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 " 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:=xlDatab ase, 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("m arketsegment") .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:=xlDatab ase, 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("markets egment") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables(ptab).PivotFields("fmt_pkg code") .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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem breaking macro into 2 subroutines
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro For Breaking Data To Different Sheets | Excel Discussion (Misc queries) | |||
breaking during a macro run | Excel Discussion (Misc queries) | |||
Problem breaking a link | Excel Discussion (Misc queries) | |||
common subroutines - add-in | Excel Programming | |||
Using ShadeAlternate Subroutines as Macro | Excel Programming |