• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Run Time Error while refreshing Pivot table

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

I am trying to assign a range to a Pivot table and refresh it.

I have recorded the below mentioned code and edited "SourceData:= _''' \\aaa\aaa\aaa\aaa\aaa\aaa\My Documents\aaa\aaa\aaa\aaa\[aaa.xls]Data R1C1:R20C26"

part with SourceData:=Amendrng. However this is resulting in bug.

The Amendrng is a valid range. I checked the address in immediate window.
Data range is in sheet 2 and Pivot table is in sheet 1 of the same file.

I am not able to understand the problem, can anyone please help me with this.

Code: 
'''    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
'''    PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
'''    "\\aaa\aaa\aaa\aaa\aaa\aaa\My Documents\aaa\aaa\aaa\aaa\[aaa.xls]Data R1C1:R20C26" _
'''    , Version:=xlPivotTableVersion10)

Code: 
ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Amendrng, Version:=xlPivotTableVersion10)
 
Hi Sachin ,

I think the parameter requires a String value ; Amendrng.Address might be needed. You might even need to pass the complete address , including the sheet name and the ! symbol and the range reference.

Narayan
 
Hi,

Just realized that I am using same code in another macro and it's working without any problem. :confused:

Code: 
Set rng10 = Sht5.Range(Sht5.Cells(1, 1), Sht5.Cells(Lr1, LastCol))
    Sht5.Range(Sht5.Cells(2, 1), Sht5.Cells(Lr1, 1)) = TheDate
    'rng10.Select
   
    Sht6.PivotTables("PivotTable7").ChangePivotCache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    rng10, Version:=xlPivotTableVersion10)
 
Hi @NARAYANK991 Sir, is it possible that macro is giving bug due to increased len size of the cell.

I just tried the same range with below mentioned formula in a column.
Macro is running fine till 256 but gives bug after 256.

"=REPT("A",257)"

My defined range has 20 rows so I was manually checking, I was stuck at row 19. Which has a len value of 387 in a Column.

I replaced original value of row 19 with lesser text and macro is working fine.

Not sure if this is the real cause but seems so.
 
Last edited:
Hi Sachin ,

I have no idea , sorry. I would however like to say that the kind of code that you are trying to use is against my principles of coding , since it does not serve any higher principle. There are too many operations going on to be able to say where the problem lies. I always believe that using intermediate variables to store intermediate results is a good practice , and helps readability and maintainability , while in no way contributing to inefficiency.

This is not the same as using unwanted Selects and Activates , which do not in any way contribute to readability.

The statement :

ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Amendrng, Version:=xlPivotTableVersion10)

is doing two operations ; one is the ChangePivotCache operation , for which a new pivot cache is required ; the second operation supplies this new pivot cache reference. If you define an intermediate variable declaring it as a PivotCache object , you can assign the output of the second operation to it , and then use this variable in the first operation.

Since you are now having two statements instead of one , it will be clearer as to which of the two statements is generating the RTE.

I belong to the school of thought that thinks the ability to code dense , obscure code is not necessarily the hallmark of a great programmer , nor is it always required.

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

Sure, as advised by you I will definately try to improve my coding.
To be honest, I was not aware I was doing two operations.

I guess, I have recorded the code or copied from soemewhere (do not remember at the moment).

I will try to improve/edit the above mentioned code. Will revert with details, issues.

Good night. Have a nice weekend. :)
 
You must log in or register to reply here.
Back
Top