Pivot table creation using VBA

Hi there,

I am trying to automate a defect tracking system using VBA and what I have is a table of data listing defect found in a testing period.

From this data I look for the halfway date and then insert a blank row and populate the same headings from Row 1 of the spreadsheet.

I then recorded a macro to get the basis of the VBA code I need.

I then created a function that has 2 data input (The row number where I inserted the new row and the last row used)

In the function I set the last row of first pivot table data then create a For next loop that creates the first pivot table and then attempts to create the second one.

When the script tried to create the 2nd pivot table I get the following error message:-

Run-time error '1004:

The Pivot Table field name is not valid. To create a Pivot Table report, you must use data that is organized as a list with labelded column. If you are changing the name of a Pivot Table field, you must type a new name for the field.

======= Function code below ===========
Function CreatePivot(ByVal NewRow, ByVal LastRow)

'Set the row details for the first pivot table and the second pivot table

'Last row for the first pivot table
firstrow = NewRow - 1

For Table = 1 To 2

Sheets("Defects").Select

If Table = 1 Then

Range("A1:J" & firstrow).Select

Else

Range("A" & NewRow & ":J" & LastRow).Select

End If




If Table = 1 Then

Range("A1:J" & firstrow).Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Defects!R1C1:R" & firstrow & "C10", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="First_Half!R3C1", TableName:="First1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("First_Half").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("First1").PivotFields("BG_SEVERITY")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("First1").PivotFields("BG_DETECTION_DATE")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("First1").AddDataField ActiveSheet.PivotTables( _
"First1").PivotFields("TOTAL_PER_DAY"), "Sum of TOTAL_PER_DAY", xlSum

ActiveWorkbook.ShowPivotTableFieldList = False

Else
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Defects!R" & NewRow & "C1:R" & LastRow & ":C10", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Second_Half!R3C1", TableName:="Second2", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Second_Half").Select
Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("Second1").PivotFields("BG_SEVERITY_2")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("Second1").PivotFields("BG_DETECTION_DATE_2")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("Second1").AddDataField ActiveSheet.PivotTables( _
"Second1").PivotFields("TOTAL_PER_DAY_2"), "Sum of TOTAL_PER_DAY", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
End If

Next

End Function
=======================================