+ Reply to Thread
Results 1 to 3 of 3

Match Type Error

Hybrid View

blincan Match Type Error 07-26-2012, 06:56 PM
shg Re: Match Type Error 07-26-2012, 07:22 PM
blincan Re: Match Type Error 07-27-2012, 05:00 PM
  1. #1
    Registered User
    Join Date
    03-21-2012
    Location
    california
    MS-Off Ver
    excel 2007
    Posts
    9

    Angry Match Type Error

    This macro is one I got offline and it work perfectly with another tab, but once I try and use a second tab with different data I keep getting an error, It might be a simple fix that I am overlooking if not underlined the spot in the code that brings back the error.

    Sub Macro1()
      Dim pt As PivotTable
      Dim strField As String
      Dim WSD As Worksheet
      Set WSD = Worksheets("aa")
      Dim PTOutput As Worksheet
      
      Sheets.Add.Name = "bb"
      
      Set PTOutput = Worksheets("bb")
      Dim PTCache As PivotCache
      Dim PRange As Range
      
      Sheets("aa").Select
      
      ' Find the last row with data
      Dim finalRow As Long
      finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
      
      ' Find the last column with data
      Dim finalCol As Long
      finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
     
     ' Find the range of the data
     ActiveWorkbook.PivotCache.MissingItemsLimit = xlMissingItemsNone
     Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
     Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange):mad:
     
     ' Create the pivot table
     Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), TableName:="SamplePivot3")
     
     ' Define the layout of the pivot table
     
     ' Set update to manual to avoid recomputation while laying outpt
      pt.ManualUpdate = True
        
        ' Set up the row fields
     pt.AddFields RowFields:=Array("ItemNumber")
    
    
    thanks for the help
     
     ' Set up the data fields
     With pt.PivotFields("ReceiptQty")
     .Orientation = xlDataField
     .Function = xlSum
     .Position = 1
     End With
     
     ' Now calc the pivot table
     pt.ManualUpdate = False
    End Sub
    Last edited by blincan; 07-27-2012 at 04:58 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Match Type Error

    Try

    Set PTCache = ActiveWorkbook.PivotCaches.Add( _
        SourceType:=xlDatabase, _
        SourceData:=PRange.Address(External:=True))
    Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    03-21-2012
    Location
    california
    MS-Off Ver
    excel 2007
    Posts
    9

    Re: Match Type Error

    Thank you very much, your solution worked perfectly, sorry for the failure to format my post

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1