Create pivot table irrespective of worksheet name

[superbuttz]

Hello! I'm brand new here and I'm a newb when it comes to VBA, so I appreciate your patience and your time.

High-level overview: I've got two categories of files/reports that auto-generate every day, let's call them Category 1 and Category 2.

All of the worksheets in Category 1 are named like: ReportABC.

All of the worksheets in Category 2 are named like: ReportDEF

The worksheet names and the workbook names are the only substantial difference between Category 1 and Category 2. Both categories have the same column headers and have the same kind of data. But both category of reports need to have a pivot table run. Obviously, I could make two separate macros to run pivot tables for each respective category, one for each category, but I'm wondering if I can create a macro that creates a pivot table, irrespective of the name of the worksheet.

I've tried to make a macro so that a pivot sheet is created based on just an active worksheet, but I haven't been able to get that to work. I feel like this would be a super easy thing to accomplish and I'm wondering how I can do that.

The other issue is that when I do create a pivot table manually, it creates Sheet1 and puts the pivot table in Sheet1. When I recorded myself creating a pivot table for the macro, it won't work more than once because it's only looking for Sheet1. Is there a way to fix that as well?

Thank you all so much for your time and consideration!