+ Reply to Thread
Results 1 to 1 of 1

Create the Pivot cache and pivot macro

  1. #1
    Registered User
    Join Date
    08-31-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    1

    Create the Pivot cache and pivot macro

    Hi below is the code. Please let me know what is wrong i am getting a run time error 13 . Page Mismatch. Below is the code. urgent help is much appriciated.
    There is nothing the pivot page field. Hence left it blank in "Pass variable names to a String variable"

    Public valr, rval, cval
    Sub VistakonCanadaandUSWeekly()
    'Getting required data
    Sheets("Raw").Select
    Range("A1").Select
    rno = ActiveCell.Column
    Selection.AutoFilter
    Selection.AutoFilter Field:=rno, Criteria1:="=Vistakon", Operator:=xlOr, _
    Criteria2:="=J&J Vision Care"
    valr = Selection.End(xlDown).Row
    rval = Selection.End(xlDown).Row
    cval = Selection.End(xlToRight).Column
    Range("A1:A" & rval + 1).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    With Worksheets.Add
    .Name = "Main"
    End With
    ActiveSheet.Paste

    'declare the row, column, page and data field variables
    Dim Pt As PivotTable
    Dim PtCache As PivotCache
    Dim pageField1 As String
    Dim pageField2 As String
    Dim pageField3 As String
    Dim rowField1 As String
    Dim rowField2 As String
    Dim colField As String
    Dim dataField As String

    'delete pivot sheet if it exists
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Pivot Sheet").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    '
    Set s = Sheets("Main")
    With Worksheets.Add
    .Name = "Pivot Sheet"
    End With

    'Pass variable names to a String variable
    pageField1 = s.Cells(1, 2).Value 'page variable ""
    rowField1 = s.Cells(1, 3).Value 'row field "District"
    colField1 = s.Cells(1, 10).Value 'colums field "Area"
    dataField = s.Cells(1, 11).Value 'data to summarize "Count of Area"

    'Name the list range
    Worksheets("Main").Activate
    ActiveSheet.Range("a1").Select
    ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
    Selection.Name = "Items"

    'create pivot cache
    Set PtCache = ActiveWorkbook.PivotCaches.Add( _
    SourceType:=xlDatabase, _
    SourceData:=s.Range("Items")) 'entire contents of sheet

    'create pivot table from cache
    Set Pt = PtCache.CreatePivotTable( _
    TableDestination:=Sheets("Pivot Sheet").Range("A3"), _
    TableName:="QualCodeTable")

    'add fields
    With Pt
    .PivotFields(rowfield1).Orientation = xlRowField 'District
    .PivotFields(pageField1).Orientation = xlPageField '
    .PivotFields(colField1).Orientation = xlColumnField 'Area
    .PivotFields(dataField).Orientation = xlDataField 'Count of Area
    End With
    Worksheets("Pivot Sheet").Columns("A:DD").AutoFit
    Application.ScreenUpdating = True

    end sub
    Last edited by krishna.chvg; 08-31-2010 at 10:06 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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