run time error 1004 in pivot table wizard

[Tharabai]

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]

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