Excel VBA code - vba runtime error 1004

[Lassebrodt]

Hi there,

I hope you are able to help me. I am rather new to the VBA universe and recently I overtook another guys makroes & code. Not ideal at all..

Anyway, the code worked fine before my holiday and nothing has been editted or added in the meantime. That is why i find it so wierd.

I am using Excel to store and keep track of a lot different data. My VBA code is used to generate several pivottables based on the data I feed the document with. Therefore, the code does not fetch any data from other document, online etc.

When I run the makroes i hit the message: "vba runtime error 1004 an item name cannot be found. Check that you typed the name correctly and the item is present in the PivotTable report".

When I hit the button "Debug" I enter the module, where I am able to edit the code. This is where I am not sure what to do. The screenshots should be able to create an overview of what I have described.

Sub CapVSOrderedPivot()
'
' Pivot02 Makro
'

      Dim PCache As PivotCache
      Dim PTable As PivotTable
      Dim PRange As Range
      Dim LastRow As Long
      Dim LastCol As Long
      Dim pt As PivotTable

      Dim pf As PivotField
      Dim pi As PivotItem
      Dim iFieldCount As Long
      Dim iPosition As Long
'
    For Each ws In Worksheets
        If ws.Name = "CapVSOrdered" Then
           Application.DisplayAlerts = False
           Sheets("CapVSOrdered").Delete
           Application.DisplayAlerts = True
        End If
    Next
    Set ws = SupplyWBK.Sheets.Add(After:=SupplyWBK.Sheets(SupplyWBK.Sheets.Count))
    ws.Name = "CapVSOrdered"
Application.DisplayAlerts = True
    Set SupplyWBK = ThisWorkbook '
'Define Data Range
LastRow = SupplyWBK.Sheets("DataX").Cells(Rows.Count, 1).End(xlUp).Row
LastCol = SupplyWBK.Sheets("DataX").Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = SupplyWBK.Sheets("DataX").Cells(1, 1).Resize(LastRow, LastCol)

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
 PRange, Version:=6).CreatePivotTable TableDestination:= _
 "CapVSOrdered!R3C1", TableName:="Kapacitet vs Bestilt", DefaultVersion:=6

    
    With ActiveSheet.PivotTables("Kapacitet vs Bestilt")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("Kapacitet vs Bestilt").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("Kapacitet vs Bestilt").RepeatAllLabels xlRepeatLabels
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("Kapacitet vs Bestilt").PivotFields("UgeNr ?r")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Kapacitet vs Bestilt").AddDataField ActiveSheet.PivotTables( _
        "Kapacitet vs Bestilt").PivotFields("Timer"), "Sum af Timer", xlSum
    With ActiveSheet.PivotTables("Kapacitet vs Bestilt").PivotFields("Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Kapacitet vs Bestilt").PivotFields("Skills")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Kapacitet vs Bestilt").PivotFields("Type").Orientation = _
        xlHidden
    With ActiveSheet.PivotTables("Kapacitet vs Bestilt").PivotFields("Type")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("Kapacitet vs Bestilt").PivotFields("Navn")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("Kapacitet vs Bestilt").PivotFields("Projekt")
        .Orientation = xlRowField
        .Position = 4
    End With
    'ActiveWindow.SmallScroll Down:=-12
    
    Collapse_Entire_RowField ("Kapacitet vs Bestilt")
    Collapse_Entire_RowField ("Kapacitet vs Bestilt")
    Collapse_Entire_RowField ("Kapacitet vs Bestilt")
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
        
    ActiveSheet.PivotTables("Kapacitet vs Bestilt").PivotFields("Sum af Timer").DataRange.Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions(1).ScopeType = xlFieldsScope


    If ActiveWindow.FreezePanes Then ActiveWindow.FreezePanes = False
    'ActiveWindow.ScrollColumn = ActiveSheet.Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column - 12
    ActiveSheet.Range("B5").Select
    ActiveWindow.FreezePanes = True
    
    ActiveWorkbook.ShowPivotTableFieldList = False
    
    WeekNoStr = Application.WorksheetFunction.WeekNum(Now)
    YearNoStr = Year(Now)
    ActiveSheet.PivotTables("Kapacitet vs Bestilt").PivotSelect "'" & YearNoStr & "-Uge " & WeekNoStr & "'", xlDataAndLabel, True
    With Selection.Font
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.599993896298105
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    
    'ActiveSheet.PivotTables("Kapacitet vs Bestilt").DataBodyRange.Select
    'Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=0"
    'Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    'With Selection.FormatConditions(1).Interior
     '   .PatternColorIndex = xlAutomatic
      '  .Color = 255
       ' .TintAndShade = 0
    'End With
    'Selection.FormatConditions(1).StopIfTrue = False

End Sub

Please let me know if this is enough information to help me solve this problem and let me know if I need to provide more.

I hope you can help me. I really need it.

Thanks,
Lasse

[DataMiser]

You need to paste the code rather than a screen shot, text of the code is scaled down to where it is to hard to read.

[Lassebrodt]

Good info. Thanks.

I have posted the code in the text instead. The yellow text in the screenshot is made bold in the text.

[gibra]

1. This section isn't for VBA. You should post in appropriate section. Ask to moderator to move this thread.
2. The code must be written using the tag CODE (well formatted code is required)
3. You should attach a file that reproduce the issue, because the ONLY way to solve is: DEBUG
4. Ensure that on 'top of module' (any) there the Option Explicit instruction, see:
https://www.excel-easy.com/vba/examp...-explicit.html

[westconn1]

you should attach a sample workbook (zip first)
to a post

a moderator will probably move this to office development forum