pass worksheet name to a macro

[Allan]

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!

[witek]

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.

[Allan]

witek wrote:
> Post your macro here. Or at least begining of it.


Sub Macro1()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, 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!

[NickHK]

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" <[email protected]> wrote in message
news:[email protected]...
>
> witek wrote:
> > Post your macro here. Or at least begining of it.
>
>
> Sub Macro1()
> ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, 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!
>