CODEWITHSUNDEEP
vbaexcelpivot-table
Zwick44
Zwick44

Reputation: 57

Passing Excel Sheet name into macro VB

I think this should be a simple one considering I have very little VB knowledge :) I have recorded a macro to create a pivot table for a range on the current sheet. Of course what this gives me is the code to create a pivot table for that specific sheet. I am trying to adjust the code to grab the name of the active sheet so I can use this macro across different sheets. I have tried to search and find exactly how to retrieve the name and then supply that name to the code that will being the process of creating the pivot table.

I am hitting a roadblock at the ActiveWorkbook.PivotCaches.Create section of the code. I am getting the "Run-time error '1004': Application-defined or object-defined error" message.

Any help would be greatly appreciated!

Sub Macro1()

Dim sht As String
sht = ActiveSheet.Name

    Columns("A:K").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Worksheets(sht).Range("!R1C1:R1048576C11"), Version:=6).CreatePivotTable TableDestination:= _
        Worksheets(sht).Range("!R1C14"), TableName:="PivotTable3", DefaultVersion:=6
    ActiveSheet.Select
    Cells(1, 14).Select
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Department")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields( _
        "Originating Master Name")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Net")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Month")
        .Orientation = xlRowField
        .Position = 4
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Net"), "Count of Net", xlCount
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Month")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of Net")
        .Caption = "Sum of Net"
        .Function = xlSum
    End With
End Sub

Upvotes: 0

Views: 382

Answers (1)

QHarr
QHarr

Reputation: 84465

Change this 

Worksheets(sht).Range("!R1C1:R1048576C11")

To this

ActiveWorkbook.Sheets(sht).Range("A1:K1048576")

And this

 Worksheets(sht).Range("!R1C14")

To this

 ActiveWorkbook.Sheets(sht).Range("N1")

Upvotes: 1

Related Questions