pass worksheet name to a macro

  • Thread starter Thread starter Allan
  • Start date Start date
A

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!
 
Allan said:
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!
Click to expand...

Post your macro here. Or at least begining of it.
 
witek said:
Post your macro here. Or at least begining of it.
Click to expand...


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!
 
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
 
Back
Top