Consulting

Results 1 to 1 of 1

Thread: Pivot Table Creation Error

  1. #1
    VBAX Newbie
    Joined
    Aug 2019
    Posts
    4
    Location

    Pivot Table Creation Error

    I am a beginner at VBA and coding all together. I have been working on coding a macro for creating a pivot table and I don't understand what is wrong with my code.

    The Bolded and Underlined line is the where the error is coming up

    Excel 2013

    ERROR CODE:

    Run-tie error '424': Object Required

    Sub CreatePivotTable()
    Dim sht As Worksheet
    Dim pvtCache As PivotCache
    Dim pvt As PivotTable
    Dim StartPvt As String
    Dim SrcData As String
    'Determine the data range you want to pivot
      SrcData = ActiveSheet.Name & "!" & Range("A1:BU99999").Address(ReferenceStyle:=xlR1C1)
    'Create a new worksheet
      Set sht = Sheets.Add
      sht.Name = ("Pivot Table")
    'Where do you want Pivot Table to start?
      StartPvt = sht.Name & "!" & sht.Range("A1").Address(ReferenceStyle:=xlR1C1)
    'Select "Data Dump"
      ThisWorkbook.Sheets("Data Dump").Select
    'Create Pivot Cache from Source Data
      Set pvtCache = ActiveWorksheet.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)
    'Create Pivot table from Pivot Cache
      Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="IAmPivot")
    pvt.PivotFields("Module").Orientation = xlRowField
    pvt.PivotFields("KW").Orientation = xlColumnField
    pvt.AddDataField pvt.PivotFields("Module"), "Anzahl von Module", xlCount
    pvt.PivotFields("Module").PivotFilters.Add Type:=xlTopCount, DataField:=pvt.PivotFields("Anzahl von Module"), Value1:=12
    End Sub
    Last edited by Aussiebear; 03-30-2023 at 02:19 PM. Reason: Added code tags to supplied code

Posting Permissions

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