dcsimg
Results 1 to 5 of 5

Thread: Excel VBA code - vba runtime error 1004

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2020
    Posts
    2

    Excel VBA code - vba runtime error 1004

    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.
    Code:
    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
    Attached Images Attached Images   
    Last edited by Shaggy Hiker; Jan 5th, 2020 at 10:44 AM. Reason: Added CODE tags.

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,024

    Re: Excel VBA code - vba runtime error 1004

    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.

  3. #3

    Thread Starter
    New Member
    Join Date
    Jan 2020
    Posts
    2

    Re: Excel VBA code - vba runtime error 1004

    Good info. Thanks.

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

  4. #4
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,680

    Re: Excel VBA code - vba runtime error 1004

    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

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,679

    Re: Excel VBA code - vba runtime error 1004

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

    a moderator will probably move this to office development forum
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width