' Assuming your data starts in column A, with headers in row 1
' and the column has no blanks
' Once the file is open

Sheet_Name = ActiveSheet.Name
Last_Row = ActiveSheet.Range("A65536").End(xlUp).Row
Your_Last_Column = 10 ' amaned this to the column number of your last
column
Pivot_Name = "YOUR PIVOT TABLE NAME"
Source_Range = "'" + Sheet_Name + "'!R1C1:R" + CStr(Last_Row) + "C" +
CStr(Your_Last_Column)
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
Source_Range).CreatePivotTable TableDestination:= _
"", TableName:=Pivot_Name, DefaultVersion:=xlPivotTableVersion10

I am assuming that you've got the rest of the pivot routine and it's just
the range that's an issue.

"Jodie" wrote:

I am trying to write a macro to create a pivot table to use in multiple files
where each file will contain the same headers and number of columns.
However, the number of rows will vary in each file. Each file has one sheet
and all of these sheets have a different name. Can anyone please help?
--
Thank you, Jodie