Pivot table creation without sheet name

[Leo55]

I have been searching a solution and cannot seem to find one, is this possible,

What I am trying to do is write a macro that will create a pivot table onto another tab.

The sheet that the data is on is titled with a customer reference number which changes from workbook to workbook.

Is it possible to create a macro that will create a pivot table on a new sheet without the need to use the data sheets name?

Thanks in advance

 

[Leo55]

I have been searching a solution and cannot seem to find one, is this possible,

What I am trying to do is write a macro that will create a pivot table onto another tab.

The sheet that the data is on is titled with a customer reference number which changes from workbook to workbook.

Is it possible to create a macro that will create a pivot table on a new sheet without the need to use the data sheets name?

Thanks in advance

Just to clarify - I want a macro that will create a Pivot table of the data contents of sheet 1 (which name changes every time). so the macro needs to not be affected by the name of the sheet being different.
The data range can be the entire sheet as I would like the pivot to capture all the data on the database.

Thanks in advance