nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,159
Hi,

I have a macro running which includes this information below. When the macro runs I get this error:

Run time error '1004'
The pivot table field name is not valid. To create a pivot table report, you must use data that is organised as a list with labeled columns. If you are changing the name of the pivot table field, you must type a new name for the field.

Would anyone have any ideas why?

Thanks in advance...


Code included within macro, full macro below.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"FILTER_DATA!R1C1:R1048576C4", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="PIVOTS!R4C169", TableName:= _
"PivotTable41", DefaultVersion:=xlPivotTableVersion12


Code:
Sub Filter_CopyPasteFilter()
Application.ScreenUpdating = False


    Sheets("FILTER_DATA").Select
    Cells.Select
    Selection.Clear
    
    Sheets("PIVOTS").Select
    Columns("Fm:fp").Select
    Selection.Clear


Sheets("DATA").Select
ActiveSheet.AutoFilter.Range.Copy
Sheets("FILTER_DATA").Select
Sheets("FILTER_DATA").Paste


Sheets("FILTER_DATA").Select
Rows("1:1").Select
Selection.ClearFormats
    
Call DeleteBlankRows
Call PIVOT_Final


End Sub


Sub DeleteBlankRows()
Sheets("FILTER_DATA").Select
 
Range("BQ2:BT5000").Select
Selection.Cut
Range("BM5001").Select
ActiveSheet.Paste


Range("BU2:BX5000").Select
Selection.Cut
Range("BM10001").Select
ActiveSheet.Paste


Range("BY2:CB5000").Select
Selection.Cut
Range("BM15001").Select
ActiveSheet.Paste


Range("CC2:CF5000").Select
Selection.Cut
Range("BM20001").Select
ActiveSheet.Paste


Columns("A:BL").Select
Range("BL1").Activate
Selection.Delete Shift:=xlToLeft
Columns("E:U").Select
Selection.Delete Shift:=xlToLeft
Range("A:D").Select




On Error Resume Next


Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete


On Error GoTo 0


End Sub


Sub PIVOT_Final()


    Sheets("PIVOTS").Select
    Range("FM4").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "FILTER_DATA!R1C1:R1048576C4", Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="PIVOTS!R4C169", TableName:= _
        "PivotTable41", DefaultVersion:=xlPivotTableVersion12
    Sheets("PIVOTS").Select
    Cells(4, 169).Select
    With ActiveSheet.PivotTables("PivotTable41").PivotFields("PART No")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable41").AddDataField ActiveSheet.PivotTables( _
        "PivotTable41").PivotFields("GOOD"), "Count of GOOD", xlCount
    ActiveSheet.PivotTables("PivotTable41").AddDataField ActiveSheet.PivotTables( _
        "PivotTable41").PivotFields("DEFECT"), "Count of DEFECT", xlCount
    ActiveSheet.PivotTables("PivotTable41").AddDataField ActiveSheet.PivotTables( _
        "PivotTable41").PivotFields("SCRAP"), "Count of SCRAP", xlCount
    With ActiveSheet.PivotTables("PivotTable41").PivotFields("Count of GOOD")
        .Caption = "Sum of GOOD"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable41").PivotFields("Count of DEFECT")
        .Caption = "Sum of DEFECT"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable41").PivotFields("Count of SCRAP")
        .Caption = "Sum of SCRAP"
        .Function = xlSum
    End With
    
    Sheets("PIVOTS").Select
    Range("FM1").Select
End Sub
 _
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Watch MrExcel Video

Forum statistics

Threads
1,109,341
Messages
5,528,147
Members
409,804
Latest member
aceyus_michael

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top