Pivot Table type mismatch
 

Hi folks,
When I run the following macro I get a type mismatch when it tries to
create the pivot table. The commented out section works but I need to
have the flexibility of using the variables for the bottom-right edge
of the data as that changes daily. The section that gives me the type
mismatch here works exactly as written in another macro. Very confused,
I am.
Thanks,
Bill

Option Explicit
Sub sort_recvd()

Dim ro As Long, col As Long
Dim FileLoc As String

'Where to look for the csv file.
FileLoc = "TEXT;" & Environ("USERPROFILE") & "\Desktop\"

' Import csv file and find its edges.
ActiveWorkbook.Sheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
FileLoc & "receipts.csv", Destination:=Range("A1"))
.Name = "Received"
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileTabDelimiter = True
.Refresh BackgroundQuery:=False
End With
ActiveSheet.Name = "Received"
ro = Cells(Rows.Count, "A").End(xlUp).Row
col = Cells(1, Columns.Count).End(xlToLeft).Column

' Build pivot table.
'= This section works but locks me into that data range.
' ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
' SourceData:="Received!R1C1:R244C30").CreatePivotTa ble _
' TableDestination:="", TableName:="Received Units"

'= This section gives a type mismatch but works as written in another
macro.
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=ActiveSheet.Range("A1", Cells(ro,
col))).CreatePivotTable _
TableDestination:="", TableName:="Received Units"
With ActiveSheet.PivotTables(1).PivotFields("Return Account")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables(1).PivotFields("Part #")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables(1).AddDataField _
ActiveSheet.PivotTables(1).PivotFields("Promised") , _
"Count of Promised", xlCount

Cells.EntireColumn.AutoFit

End Sub

Pivot Table type mismatch
 

Well, since no one seemed to have any ideas I did some more work on
this and found a work-around.

These lines are inserted in the appropriate sections of teh code.

myrng="R" & ro & "C" & col

And then;
SourceData:="Received!R1C1:" & myrng

That gives me the flexibility I need and keeps the dreaded error 13 at
bay.

Bill

Bill wrote:
Hi folks,
When I run the following macro I get a type mismatch when it tries to
create the pivot table. The commented out section works but I need to
have the flexibility of using the variables for the bottom-right edge
of the data as that changes daily. The section that gives me the type
mismatch here works exactly as written in another macro. Very confused,
I am.
Thanks,
Bill

Option Explicit
Sub sort_recvd()

Dim ro As Long, col As Long
Dim FileLoc As String

'Where to look for the csv file.
FileLoc = "TEXT;" & Environ("USERPROFILE") & "\Desktop\"

' Import csv file and find its edges.
ActiveWorkbook.Sheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
FileLoc & "receipts.csv", Destination:=Range("A1"))
.Name = "Received"
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileTabDelimiter = True
.Refresh BackgroundQuery:=False
End With
ActiveSheet.Name = "Received"
ro = Cells(Rows.Count, "A").End(xlUp).Row
col = Cells(1, Columns.Count).End(xlToLeft).Column

' Build pivot table.
'= This section works but locks me into that data range.
' ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
' SourceData:="Received!R1C1:R244C30").CreatePivotTa ble _
' TableDestination:="", TableName:="Received Units"

'= This section gives a type mismatch but works as written in another
macro.
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=ActiveSheet.Range("A1", Cells(ro,
col))).CreatePivotTable _
TableDestination:="", TableName:="Received Units"
With ActiveSheet.PivotTables(1).PivotFields("Return Account")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables(1).PivotFields("Part #")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables(1).AddDataField _
ActiveSheet.PivotTables(1).PivotFields("Promised") , _
"Count of Promised", xlCount

Cells.EntireColumn.AutoFit

End Sub