Run Type error 13,Type Mismatch in macro

M

mable thomas

New Member
Joined
Aug 15, 2013
Messages
3
Hi,

I get Run time error 13,Type mismatch while trying to run the below macro code.
I am actually trying to create a pivot table using macro.But I get the above error when I do so in the code line given in bold.Cud anyone help pls.. It's urgent.Pls note that there is around 85,000 rows of data in the dummy sheet.

Code is:
Public Sub CreatePivotTable()
Dim pt As PivotTable
Dim PtCache As PivotCache
Dim pageField1 As String
Dim rowField1 As String
Dim rowField2 As String
Dim colField As String
Dim dataField As String
Dim ws As Worksheet, ws1 As Worksheet
Dim Rng As Range


'delete pivot sheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Pivot Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

With Worksheets.Add
.Name = "Pivot Sheet"
End With


Set ws1 = Worksheets("Dummy")

ws1.Activate
lRow3 = ws1.Range("A" & ws.Rows.Count).End(xlUp).Row

pageField1 = ws1.Cells(1, 2).Value
rowField1 = ws1.Cells(1, 6).Value
colField1 = ws1.Cells(1, 6).Value
dataField = ws1.Cells(1, 14).Value

Worksheets("Dummy").Activate
ActiveSheet.Range("A1:V1" & lRow3).Select
Selection.Name = "Items"


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

'create pivot table from cache
'PtCache.Refresh
Set pt = PtCache.CreatePivotTable(TableDestination:=Sheets("Pivot Sheet").Range("A3"), TableName:="Pivot Table")
PtCache.Refresh
'add fields
With pt

.PivotFields(rowField1).Orientation = xlRowField 'Op No
.PivotFields(colField1).Orientation = xlColumnField 'Defect
.PivotFields(dataField).Orientation = xlDataField 'sum of Tons
.PivotFields(pageField1).Orientation = xlPageField
End With
Worksheets("Pivot Sheet").Columns("A:DD").AutoFit

On Error Resume Next
Application.DisplayAlerts = False
On Error GoTo 0
Application.DisplayAlerts = True

Sheets("Pivot Sheet").Activate
Sheets("Pivot Sheet").Range("B6").Select 'selects a cell in the pivot table
Set ws1 = Worksheets("Pivot Sheet")
ws1.PivotTables("Pivot Table").ManualUpdate = True

With ws1.PivotTables("Pivot Table").DataBodyRange
.Offset(-1, -1).Copy ws1.Range("F1:F1")
End With
ws1.PivotTables("Pivot Table").ManualUpdate = False

End Sub
 
Sort by date Sort by votes
You have an extra 1 in this line of code.
ActiveSheet.Range("A1:V1" & lRow3).Select

Replace this...
Worksheets("Dummy").Activate
ActiveSheet.Range("A1:V1" & lRow3).Select
Selection.Name = "Items"

With this...
ws1.Range("A1:V" & lRow3).Name = "Items"
 
Upvote 0
Show your code surrounded by
Code: 
 tags so the formatting is retained.

Surround your pasted VBA code with code tags e.g.;

[PLAIN][CODE]your VBA code here
[/PLAIN]

The pound # icon in the Advanced forum editor will apply the code tags around the highlighted text.
 
Upvote 0
Code: 
Public Sub CreatePivotTable()
Dim pt As PivotTable
Dim PtCache As PivotCache
Dim pageField1 As String
Dim rowField1 As String
Dim rowField2 As String
Dim colField As String
Dim dataField As String
Dim ws As Worksheet, ws1 As Worksheet
Dim Rng As Range


'delete pivot sheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Pivot Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

With Worksheets.Add
.Name = "Pivot Sheet"
End With


Set ws1 = Worksheets("Dummy")

ws1.Activate
lRow3 = ws1.Range("A" & ws.Rows.Count).End(xlUp).Row

pageField1 = ws1.Cells(1, 2).Value
rowField1 = ws1.Cells(1, 6).Value 
colField1 = ws1.Cells(1, 6).Value 
dataField = ws1.Cells(1, 14).Value 

Worksheets("Dummy").Activate
ActiveSheet.Range("A1:V1" & lRow3).Select
Selection.Name = "Items"


'create pivot cache
[B]Set PtCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _[/B]
[B]SourceData:=ws1.Range("Items"))[/B] selected contents of sheet

'create pivot table from cache
'PtCache.Refresh
Set pt = PtCache.CreatePivotTable(TableDestination:=Sheets("Pivot Sheet").Range("A3"), TableName:="Pivot Table")
PtCache.Refresh
'add fields
With pt

.PivotFields(rowField1).Orientation = xlRowField 'Op No
.PivotFields(colField1).Orientation = xlColumnField 'Defect
.PivotFields(dataField).Orientation = xlDataField 'sum of Tons
.PivotFields(pageField1).Orientation = xlPageField
End With
Worksheets("Pivot Sheet").Columns("A:DD").AutoFit

On Error Resume Next
Application.DisplayAlerts = False
On Error GoTo 0
Application.DisplayAlerts = True

Sheets("Pivot Sheet").Activate
Sheets("Pivot Sheet").Range("B6").Select 'selects a cell in the pivot table
Set ws1 = Worksheets("Pivot Sheet")
ws1.PivotTables("Pivot Table").ManualUpdate = True

With ws1.PivotTables("Pivot Table").DataBodyRange
.Offset(-1, -1).Copy ws1.Range("F1:F1")
End With
ws1.PivotTables("Pivot Table").ManualUpdate = False

End Sub
 
Upvote 0
Code: 
[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] CreatePivotTable()
    [COLOR=darkblue]Dim[/COLOR] pt     [COLOR=darkblue]As[/COLOR] PivotTable
    [COLOR=darkblue]Dim[/COLOR] PtCache [COLOR=darkblue]As[/COLOR] PivotCache
    [COLOR=darkblue]Dim[/COLOR] pageField1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] rowField1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] rowField2 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] colField [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] dataField [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet, ws1 [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] Rng    [COLOR=darkblue]As[/COLOR] Range
    
    
    [COLOR=green]'delete pivot sheet if it exists[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
    Sheets("Pivot Sheet").Delete
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Worksheets.Add
        .Name = "Pivot Sheet"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] ws1 = Worksheets("Dummy")
    
    ws1.Activate
    lRow3 = ws1.Range("A" & ws.Rows.Count).End(xlUp).Row
    
    pageField1 = ws1.Cells(1, 2).Value
    rowField1 = ws1.Cells(1, 6).Value
    colField1 = ws1.Cells(1, 6).Value
    dataField = ws1.Cells(1, 14).Value
    
    Worksheets("Dummy").Activate
    [B]ActiveSheet.Range("A1:V" & lRow3).Select[/B]
    Selection.Name = "Items"
    
    [COLOR=green]'create pivot cache[/COLOR]
[B]    [COLOR=darkblue]Set[/COLOR] PtCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
        SourceData:=ws1.Range("Items"))    [COLOR=green]'selected contents of sheet[/COLOR][/B]
    
    [COLOR=green]'create pivot table from cache[/COLOR]
    [COLOR=green]'PtCache.Refresh[/COLOR]
    [COLOR=darkblue]Set[/COLOR] pt = PtCache.CreatePivotTable(TableDestination:=Sheets("Pivot Sheet").Range("A3"), TableName:="Pivot Table")
    PtCache.Refresh
    [COLOR=green]'add fields[/COLOR]
    [COLOR=darkblue]With[/COLOR] pt
        .PivotFields(rowField1).Orientation = xlRowField    [COLOR=green]'Op No[/COLOR]
        .PivotFields(colField1).Orientation = xlColumnField    [COLOR=green]'Defect[/COLOR]
        .PivotFields(dataField).Orientation = xlDataField    [COLOR=green]'sum of Tons[/COLOR]
        .PivotFields(pageField1).Orientation = xlPageField
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    Worksheets("Pivot Sheet").Columns("A:DD").AutoFit
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
    
    Sheets("Pivot Sheet").Activate
    Sheets("Pivot Sheet").Range("B6").Select    [COLOR=green]'selects a cell in the pivot table[/COLOR]
    [COLOR=darkblue]Set[/COLOR] ws1 = Worksheets("Pivot Sheet")
    ws1.PivotTables("Pivot Table").ManualUpdate = [COLOR=darkblue]True[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] ws1.PivotTables("Pivot Table").DataBodyRange
        .Offset(-1, -1).Copy ws1.Range("F1:F1")
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    ws1.PivotTables("Pivot Table").ManualUpdate = [COLOR=darkblue]False[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Looking to Set All pivot table filter fields to same selection using VBA
Replies
1
Views
265
jkpieterse
jkpieterse
R
  • Question Question
VBA Pivot | Not Recognizing Headers
Replies
0
Views
37
rachel06
R
R
  • Question Question
"Out of Memory" Error when trying to pivot
Replies
2
Views
88
rachel06
R
J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
505
Jette3173
J
A
  • Solved
Run-time error '13': Type mismatch with Ucase
Replies
3
Views
77
Peter_SSs
Peter_SSs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top