VBA Pivot Table Grand Total Show Detail - Error message "Run-time error '424': Object Required"

[ASHEXCEL]

Hello All,

First time posting so bear with.

I have a spreadsheet (Global Template) with a pivot table referencing data from another file (not open and a year to date file information).

I want my macro to show the detail of the grand total on a new sheet, I have the code in place for this.

The only problem is that when you first run the macro I get the error message (subsequent run of the macros there is no error message.

Run-time error '424':

Object Required

This only happens when i have another action after the show detail. I if i do not have another action then it works fine.

I am confused as i need further actions.

My coding (which i found on research yesterday, can't remember but very grateful to the individual) is

Sub ShowGrandTotalDetail()'--shows grand total drill down detail of first pivotTable
'    in activesheet.
 
 Dim sMessage As String
 
 With ActiveSheet.PivotTables(1)
   '--validate requirements met to allow grand totals drilled down
   Select Case True
      Case .DataFields.Count = 0
         sMessage = "Must have at least one DataField."
      Case .RowFields.Count + .ColumnFields.Count = 0
         sMessage = "Must have at least one RowField or ColumnField."
      Case .RowFields.Count And Not .RowGrand
         sMessage = "Grand Totals are Off for Rows."
      Case .ColumnFields.Count And Not .ColumnGrand
         sMessage = "Grand Totals are Off for Columns."
      Case Else
         '--ok- requirements met
   End Select
   
   If Len(sMessage) = 0 Then
     With .TableRange1
        '--drill down on last cell in pivot
        .Cells(.Rows.Count, .Columns.Count).ShowDetail = True
     End With
   Else
      MsgBox Prompt:=sMessage, Title:="Grand Totals detail can't be shown", _
         Buttons:=vbExclamation
   End If
 End With


End Sub

This works fine until i add this line of code before the End Sub

Sheet7.Name = "GL DATA"

For reference sheet7 is the name of the sheet created following the show detail whilst sheet2 is the pivot table.

Any help would be most approeciated as i am getting very frustrated and have the lack of understanding.

Cheers

 

[RoryA]

You can't refer to the code name of a sheet that doesn't exist when the code begins. You'd need to use ActiveSheet instead.

 

[ASHEXCEL]

You can't refer to the code name of a sheet that doesn't exist when the code begins. You'd need to use ActiveSheet instead.

RoryA Many thanks for this so simple solution works a treat

Problem Solved. happy Chappy

 

[WojtekP]

Hello,

I am trying to use this VBA to all excel files under one folder (loop) but it is not working.. All the files have one sheet with only one pivot table.

Sub LoopThroughFiles()
Dim xFd As FileDialog
Dim xFdItem As Variant
Dim xFileName As String
Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
If xFd.Show = -1 Then
xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
xFileName = Dir(xFdItem & "*.xls*")
Do While xFileName <> ""
With Workbooks.Open(xFdItem & xFileName)

Dim sMessage As String

With ActiveSheet.Pivottable(1)
'--validate requirements met to allow grand totals drilled down
Select Case True
Case .DataFields.Count = 0
sMessage = "Must have at least one DataField."
Case .RowFields.Count + .ColumnFields.Count = 0
sMessage = "Must have at least one RowField or ColumnField."
Case .RowFields.Count And Not .RowGrand
sMessage = "Grand Totals are Off for Rows."
Case .ColumnFields.Count And Not .ColumnGrand
sMessage = "Grand Totals are Off for Columns."
Case Else
'--ok- requirements met
End Select

If Len(sMessage) = 0 Then
With .TableRange1
'--drill down on last cell in pivot
.Cells(.Rows.Count, .Columns.Count).ShowDetail = True

End With
xFileName = Dir

Loop
End If
End Sub


Could anyone help please?

 

[RoryA]

It should be ActiveSheet.Pivottables(1) not ActiveSheet.Pivottable(1)

 

[WojtekP]

It should be ActiveSheet.Pivottables(1) not ActiveSheet.Pivottable(1)

Thanks a lot!

I changed the code:

Sub LoopThroughFiles()
    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xls*")
        Do While xFileName <> ""
            With Workbooks.Open(xFdItem & xFileName)
            

 With ActiveSheet.PivotTables(1)
   '--validate requirements met to allow grand totals drilled down
   Select Case True
      Case .DataFields.Count = 0
         sMessage = "Must have at least one DataField."
      Case .RowFields.Count + .ColumnFields.Count = 0
         sMessage = "Must have at least one RowField or ColumnField."
      Case .RowFields.Count And Not .RowGrand
         sMessage = "Grand Totals are Off for Rows."
      Case .ColumnFields.Count And Not .ColumnGrand
         sMessage = "Grand Totals are Off for Columns."
      Case Else
         '--ok- requirements met
   End Select
   
   If Len(sMessage) = 0 Then
     With .TableRange1
        '--drill down on last cell in pivot
        .Cells(.Rows.Count, .Columns.Count).ShowDetail = True

End With
            xFileName = Dir
            
        Loop
    End If
End Sub

However I am getting the "compile error loop without do"
I tried to google it but am very new to VBA and unable to edit my script