Help editing MACRO to run on any active sheet

Hi,

I created the macro below which inserts a pivot table. I would like it to run on any active sheet and can't figure out how. It is saved in the Personal category so it is not attached to any workbook which is what I want, but the code shows the name of the original sheet (Employee Total Hours Report) that was open when I recorded the macro. I've tried replacing with active.sheet but can't get it to work. Any help would be appreciated.

HTML Code: 

Welcome to the board.

Please take a few minutes to read the Forum Rules, and then edit your post to wrap your code with CODE tags.

Thanks.

EDIT: Thank you.

I do no pivot table automation at all, but perhaps

Please Login or Register  to view this content.

Originally Posted by Peter_Excel

Whoever provides a working solution will become famous.

In that case, you have the opportunity to share in the glory by giving a better response than "doesn't work", like what happened when you ran it, what happened when you stepped through it, what line errored, what the error message was, ...

Then you could post a workbook with sensitive data removed that illustrates.

Alright shg, you asked and you have received, but I will take none of the glory. Included is the VBA code and the error I got. I will attatch a dummy sheet if I can get the button to work. The first big paragraph of the code was highlighted yellow.

PHP Code: 

Sub Hours_Report_PivotTable()
'
' Hours_Report_PivotTable Macro
' Creates pivot table for weekly hours report
'

'
    Dim wks As Worksheet
    
    Set wks = ActiveSheet
    
    With wks
    Columns("A:J").Select
    .Parent.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=.Range("E:E"), _
        Version:=xlPivotTableVersion14 _
        ).CreatePivotTable TableDestination:=.Range("M4"), _
        TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion14
    
    With .PivotTables("PivotTable3")
      With .PivotFields("File Number")
        .Orientation = xlRowField
        .Position = 1
      End With

      With .PivotFields("Pay Code")
        .Orientation = xlColumnField
        .Position = 1
      End With

      With .PivotFields("Hours")
        .Caption = "Count of Hours"
        .Function = xlCount
      End With

      With .PivotFields("Count of Hours")
        .Caption = "Sum of Hours"
        .Function = xlSum
      End With
    End With

    .Parent.ShowPivotTableFieldList = False
  End With
End Sub 


Attachment 630179

Attachment 630180