Dynamically use sheet names for pivot tables.

Hello,

I'm currently working on a macro that will loop through my sheets and create pivot tables for each sheet. I have the pivot macros down now I'm having trouble getting it to work across all sheets dynamically. The below code works for one sheet that I pre-defined.
How would I create the Pivot table so that it would be dynamic. I want the macro to create the source data based off the worksheet that it's pulling from. Also i want the destination sheet to be named the same as the source sheet, except to add pivot at the end. so sheet 1 would also have sheet 1 pivot that would contain the pivot table.
Basically how do I write the code so that the yellow sections are set to be dynamic and based off the sheet that it is creating the pivot table from?

If i have a loop

Please Login or Register  to view this content.

Since I don't have a workbook this is "air code." Assuming the rest of your code works, this should work. Also the code as you have it will try to create multiple PivotTable1. This might cause a problem. I took care of that with a counter. You may want to name the pivot table something more descriptive.

Please Login or Register  to view this content.

I found through Googling the below code and it works! Thanks for your help regardless.

Please Login or Register  to view this content.