Creating pivot table with Recorded Macro

I have a macro that creates a PivotTable and it works great. The only issue is that it creates the pivot table by calling "Sheet2". If I try to do it again, within the document it gets an error because the next sheet will be sheet3 or sheet4 depending on what other macro has been run.

Please Login or Register  to view this content.

The section in question is:

Please Login or Register  to view this content.

I am trying to figure out if I can make it just use the next sheet in line weather it is Sheet2 or Sheet102.

Thanks for any help.

The pivot table is created fine with the code that I have, I just need to know how to change the code so that it doesn't require Sheet2. It will use whatever sheet is next in line.

It is not good idea to run the PT on active sheet, but if you want, you can replace
Sheets("Sheet2").Select
with

ActiveSheet.Select which means your cursor has to be on the active sheet before you run the code.

That also will not fix the issue, does anyone know how to reset the Sheet.Count? I have a reset button in my workbook, if i can add to that some code that will reset the sheet count back to sheet1 then this issue will be resolved....sort of