B
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
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