Djblois
09-09-2006, 06:51 AM
Here is my code:
Set findString = Range("C1").Find(What:="Sales Report")
Set wB(1) = ActiveWorkbook
Application.ScreenUpdating = True
On Error Resume Next
Set detail = Worksheets("detail")
If findString Is Nothing Then
If detail Is Nothing Then
cantrun.Show
End
Else
Set findString = detail.Range("D1").Find(What:="Whse")
If findString Is Nothing Then
cantrun.Show
End
Else
SalesReports.Show
End
End If
End If
End If
Set findString = Nothing
Set detail = wB(1).ActiveSheet
detail.Name = "Detail"
Blinco
Application.ScreenUpdating = True
'Create sheet
On Error Resume Next
SalesReports.Hide
myInput = InputBox("What do you want to name the Report?")
If (myInput) = "" Then
MsgBox "You did not Create a Sales Report. Press Blinco button to rerun report."
End
Else
End If
Set pvt = Worksheets.Add(, detail, 1)
pvt.Name = myInput
pvt.Activate
finalRow = detail.Cells(65536, 1).End(xlUp).Offset(-1, 0).Row
Set pRange = detail.Cells(1, 1).Resize(finalRow, 21)
Set ptCache = wB(1).PivotCaches.Add(SourceType:=xlDatabase, SourceData:=pRange.Address)
Set pt = ptCache.CreatePivotTable(TableDestination:=Range("A1"), TableName:=myInput)
pt.RowGrand = False
pt.ManualUpdate = True
then it loads a form and here is the code in the form
If CasesDollars.Cases.Value = True Then
With pt.PivotFields("Cases")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
End If
If CasesDollars.Units.Value = True Then
With pt.PivotFields("Units")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
End If
If CasesDollars.Amount.Value = True Then
With pt.PivotFields("Amt ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
End If
If CasesDollars.Cost.Value = True Then
With pt.PivotFields("Total Cost ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
End If
If CasesDollars.Profit.Value = True Then
With pt.PivotFields("Profit ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
End If
If CasesDollars.Price.Value = True Then
With pt.PivotFields("Price ($)")
.Orientation = xlDataField
.Function = xlAverage
.NumberFormat = "Comma"
End With
End If
After I use this to create one pivottable off of a Spreadsheet it gives me the "Object Variable or with block not set" error on line
With pt.PivotFields("Cases")
I don't understand why as pt is set and it always works the first time and it used to work. Please Help, this is very important
Set findString = Range("C1").Find(What:="Sales Report")
Set wB(1) = ActiveWorkbook
Application.ScreenUpdating = True
On Error Resume Next
Set detail = Worksheets("detail")
If findString Is Nothing Then
If detail Is Nothing Then
cantrun.Show
End
Else
Set findString = detail.Range("D1").Find(What:="Whse")
If findString Is Nothing Then
cantrun.Show
End
Else
SalesReports.Show
End
End If
End If
End If
Set findString = Nothing
Set detail = wB(1).ActiveSheet
detail.Name = "Detail"
Blinco
Application.ScreenUpdating = True
'Create sheet
On Error Resume Next
SalesReports.Hide
myInput = InputBox("What do you want to name the Report?")
If (myInput) = "" Then
MsgBox "You did not Create a Sales Report. Press Blinco button to rerun report."
End
Else
End If
Set pvt = Worksheets.Add(, detail, 1)
pvt.Name = myInput
pvt.Activate
finalRow = detail.Cells(65536, 1).End(xlUp).Offset(-1, 0).Row
Set pRange = detail.Cells(1, 1).Resize(finalRow, 21)
Set ptCache = wB(1).PivotCaches.Add(SourceType:=xlDatabase, SourceData:=pRange.Address)
Set pt = ptCache.CreatePivotTable(TableDestination:=Range("A1"), TableName:=myInput)
pt.RowGrand = False
pt.ManualUpdate = True
then it loads a form and here is the code in the form
If CasesDollars.Cases.Value = True Then
With pt.PivotFields("Cases")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
End If
If CasesDollars.Units.Value = True Then
With pt.PivotFields("Units")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
End If
If CasesDollars.Amount.Value = True Then
With pt.PivotFields("Amt ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
End If
If CasesDollars.Cost.Value = True Then
With pt.PivotFields("Total Cost ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
End If
If CasesDollars.Profit.Value = True Then
With pt.PivotFields("Profit ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
End If
If CasesDollars.Price.Value = True Then
With pt.PivotFields("Price ($)")
.Orientation = xlDataField
.Function = xlAverage
.NumberFormat = "Comma"
End With
End If
After I use this to create one pivottable off of a Spreadsheet it gives me the "Object Variable or with block not set" error on line
With pt.PivotFields("Cases")
I don't understand why as pt is set and it always works the first time and it used to work. Please Help, this is very important