VBA to select all data on a sheet, varies each time, then create pivot

[LauraWork]

hi all

I recorded a Macro and I'm chaning all the parts to work regardless of how much data I have but I cant get the pivot to work, I just need it to select all the data on the sheet which will always have 19 columns but the row number will vary each time I run the report.

how can I amend this: I have trieds lots of solutions from here and other places and cant get anything to work. I cant preselect as this shows the word 'blank' and I cant have that in the report: I also need to make sure the pivot is on a new sheet.

Heres the snapshot:

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R2007C17", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet1").Select
Cells(3, 1).Select

thanks in advance

 

[par60056]

1st quick guess:
check the sheet name in the destination.

Above code:
Sheets("Overzicht").Select

destination:
TableDestination:="Overview!R1C1"​Should those be the same?

 

[BioExcel]

1st quick guess:
check the sheet name in the destination.

Above code:
Sheets("Overzicht").Select

destination:
TableDestination:="Overview!R1C1"​Should those be the same?

would have been easy idd. but even with this adjustment I receive a new error:

run-time error '1004'

the pivottable field name is not valid. to create a pivottable report, you must use data that is organized as a list with labeled columns. if you are changing the name of a Pivottable field, you must type a new name for the field.

seem strange, as this data is correct.