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
Bookmarks