Pivot table

[MJA]

Hello,

Sub Macro1()
Range("F2").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"MARTIN!R2C1:R155C8"
End Sub

Above stands a part of a macro to create a pivot table. With the following I have a problem:
SourceData:="MARTIN!R2C1:R155C8"

If the sheet has a name other then MARTIN I get an error.
I want to play the macro whatever the sheet name is!
How can I make that happem?!

 

[Guest]

If you want it to appear on the active sheet, use the following line instead of the one containing MARTIN!, you'll need to make sure of the range too though, otherwise you'll get an error: -

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"" & ActiveSheet.Name & "!" & "R2C1:R155C8"

 

[Mudface]

That was from me (so I take full responsibility ).

 

[Mudface]

Actually, thinking about it, the following should work for any sheet which has listed data in column A, (change the Range("A1").Select statement to reflect your data position): -

Sub Macro1()
Dim myRange as Range

Range("A1").Select
Set myRange = ActiveCell.CurrentRegion
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"" & ActiveSheet.Name & "!" & myRange.Address(ReferenceStyle:=xlR1C1) & ""
End Sub