[SOLVED] Pivot Table type mismatch

[Bill]

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:=xlDatabase, _
' SourceData:="Received!R1C1:R244C30").CreatePivotTable _
' TableDestination:="", TableName:="Received Units"

'=> This section gives a type mismatch but works as written in another
macro.
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
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

[Bill]

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:=xlDatabase, _
> ' SourceData:="Received!R1C1:R244C30").CreatePivotTable _
> ' TableDestination:="", TableName:="Received Units"
>
> '=> This section gives a type mismatch but works as written in another
> macro.
> ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
> 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