Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pass worksheet name to a macro
Hi,
I recorded a macro to create a pivot table for a worksheet. but I want use this macro for more than one worksheet, that individual worksheet name is required to pass to this macro. Could you please give some me some ideas on how to pass the worksheet name to the macro? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pass worksheet name to a macro
Allan wrote:
Hi, I recorded a macro to create a pivot table for a worksheet. but I want use this macro for more than one worksheet, that individual worksheet name is required to pass to this macro. Could you please give some me some ideas on how to pass the worksheet name to the macro? Thanks! Post your macro here. Or at least begining of it. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
pass worksheet name to a macro
witek wrote: Post your macro here. Or at least begining of it. Sub Macro1() ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Sheet1'!R5C1:R138C5").CreatePivotTable TableDestination:="", _ TableName:="PivotTable1" ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _ "Name", ColumnFields:="Level" ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count").Orientation = _ xlDataField End Sub How to make this code to handle : Sheet1, Sheet2 ...... with their ranges ? Thank you! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
pass worksheet name to a macro
Allan,
I don't use pivot table, but you need to pass in any required info, using those objects in place of the current "ActiveWorkbook", "ActiveSheet" etc. So: Sub MakePivot(argSheet as Worksheet, argDataRange as range,argDestinationRange as range) With argSheet.Parent .PivotCaches.Add(SourceType:=xlDatabase, SourceData:="'" & argSheet.name & "'!" & argDataRange.address..... etc NickHK "Allan" wrote in message ups.com... witek wrote: Post your macro here. Or at least begining of it. Sub Macro1() ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Sheet1'!R5C1:R138C5").CreatePivotTable TableDestination:="", _ TableName:="PivotTable1" ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _ "Name", ColumnFields:="Level" ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count").Orientation = _ xlDataField End Sub How to make this code to handle : Sheet1, Sheet2 ...... with their ranges ? Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pass to textbox on a form and to excel worksheet | New Users to Excel | |||
Pass contents of a cell to a macro | Excel Programming | |||
Pass variable to macro | Excel Programming | |||
Pass worksheet to macro as a parameter | Excel Programming | |||
pass argument to macro | Excel Programming |