Unable to set the ShowDetail property of the Range class

[ThrottleWorks]

Hi,

I am getting a bug while trying to run below mentioned line.

The problem is, when I run the macro by doing F8, the code will throw bug at the first instance.

When I press the F8 key again (without doing anything) the macro runs fine and does not throw any bug.

I am getting error message as Run-time error '1004':
Unable to set the ShowDetail property of the Range class.

Can anyone please help me in this

Pvt91RngGrand.ShowDetail = True 'This line will export Pivot table

 

[NARAYANK991]

Hi Sachin ,

What does Pvt91RngGrand refer to ? When posting a VBA question , it would help if you could post the complete macro rather than just the line which generates an error.

Narayan

 

[ThrottleWorks]

Hi @NARAYANK991 Sir, my mistake, won't happen again.

Please see below code if you get time.

I am trying to export multiple pivot tables which are located in a single sheet.
There are approx. 15 tables and I am trying to export 5 of them.

I am using below mentioned code to export one of the Pivot table.

'Export Pivot Table 1 that is "AAA"
    Application.StatusBar = "exporting previous Pivot tables"
    Dim Pvt91Rng As Range
    Dim Pvt91RngGrand As Range
    Dim Pvt91 As PivotTable
  
    File3.Activate
    Set Pvt91 = Prev_aaa_Dashboard.PivotTables("AAA")
    Set Pvt91Rng = Pvt91.DataBodyRange
  
    'Check if user has filtered everything away
    If Pvt91Rng Is Nothing Then Exit Sub
  
    Set Pvt91RngGrand = Pvt91Rng.Cells(Pvt91Rng.Cells.Count)
    Pvt91RngGrand.ShowDetail = True 'This line will export Pivot table

 

[NARAYANK991]

Hi Sachin ,

When you get the error , can you see what you get when you type the following in the Immediate window after clicking on the Debug button ?

?Pvt91RngGrand.Address

What is displayed ? Is this what you would expect ?

Narayan

 

[ThrottleWorks]

Hi @NARAYANK991 Sir, thanks a lot for the help.

I tried as per your advise, when I got the bug, Im Window gave me result as "$R$140" which is incorrect.

However when the macro runs without any bug (it just did, do not know how).
It gave me result as "$R$37" which is correct.

Just want to understand one thing, when we hit "?Pvt91RngGrand.Address" in Immediate window, it should give me address of last cell in the Pivot, right ?

The Pivot which I am trying to export has it's last cell on R37.

When there was bug, Immediate window result was $R$140 which is last cell of another Pivot.

 

[NARAYANK991]

Hi ,

Only you can debug , since there is no file available.

After the following statement has been executed , what pivot table is selected ?

Set Pvt91Rng = Pvt91.DataBodyRange

Suppose in the Immediate window , you type in :

?Pvt91Rng.Address

what is displayed ?

It is possible the error is due to the following statement :

Set Pvt91RngGrand = Pvt91Rng.Cells(Pvt91Rng.Cells.Count)

Narayan

 

[ThrottleWorks]

Hi @NARAYANK991 Sir, thanks a lot for the help. I am checking this and will revert with details.

Have a nice day ahead.

 

[ThrottleWorks]

Hi @NARAYANK991 Sir,

I am supposed to export 5+5 Pivot tables from 2 different files.

5 tables from current file, 5 tables from old file, then reconcile it.

I am not sure, but it seems that one of file has some issue.

When I changed one file, macro is running fine.
I am trying to find the reason / or is this the real reason.

Will share the details shortly.

 

[ThrottleWorks]

Hi @NARAYANK991 Sir,

I have made 1 change in my code now.

"
File1.Activate
Breaks_Dashboard.Select
Range("a1").Select"

Honestly I do not know the reason, but it seems to be working now.

Thanks a lot for your help and valuable time.

Application.StatusBar = "1. ..."
    Dim AAACashRng As Range
    Dim AAACashRngGrand As Range
    Dim AAACashPvt As PivotTable

    File1.Activate
    Breaks_Dashboard.Select
    Range("a1").Select
    Set AAACashPvt = Breaks_Dashboard.PivotTables("AAACash")
    Set AAACashRng = AAACashPvt.DataBodyRange

    'Check if user has filtered everything away
    If AAACashRng Is Nothing Then Exit Sub

    Set AAACashRngGrand = AAACashRng. Cells(AAACashRng.Cells.Count)

    AAACashRngGrand. ShowDetail = True