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!

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.

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!

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!