PDA

View Full Version : run time error 1004 in pivot table wizard



Tharabai
09-22-2015, 04:40 PM
Hi,

When I run a macro, am getting run time error message 1004 in the pivot table wizard as I found only header with data.

if there are no data to put a pivot the results should be displayed as "No results" by inserting a new worksheet.

Below is the code where I get an error message.



Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("Sheet1").Select
Range("A1").Select

Set objTable = Sheets("Sheet1").PivotTableWizard 'Mscro stops here with the error message

With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = CalcMode
End With


-Tharabai

p45cal
09-23-2015, 02:34 PM
Long shot, but perhaps something like:
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("Sheet1").Select

If Range("A1").CurrentRegion.Rows.Count > 1 Then
Range("A1").Select
Set objTable = Sheets("Sheet1").PivotTableWizard 'Mscro stops here with the error message
Else
Set NewSht = Sheets.Add '(After:=Sheets(Sheets.Count))
NewSht.Range("A1").Value = "No Results"
End If