Copy cells from dynamic Pivot Table

[Youri98]

Good afternoon,

I’m new to UiPath and still learning a lot. At the moment I’m trying to do some Excel automation. I got most of the steps working, but some of them were hard coded (like copying sells to a different sheet).

Now, I’m trying to make this dynamic. I need to copy the totals from column E, F and G. The totals are in the last row of the Pivot Table. I tried the ‘Find First/Last Data Row’ to find the last row and then copy the data with the ‘Copy/Paste Range’. I also tried the ‘Read Range’ together with ‘Write Range’. In both cases I get ‘error BC30331: Value of type ‘Integer’ cannot be converted to ‘IReadRangeRef’. The selected value is incompatible with the property type.’

Anyone got a clue how I could fix this?

Thanks in advance!

Youri

[ashokkarale]

@Youri98,

Kindly share screenshot of logic you implemented to see how you are using the read range approach.

[pratik.santani]

Hi, is it possible to share the code and excel file here? Maybe you are not entering the range correctly and that is what the error message is.

[Youri98]

I can’t really share the file due to confidential information. I could recreate the file with ‘fake’ information in it if needed.

I tried to find the last data row from the pivot table in 1.2.4, then I selected column B as this is where the pivot table starts and saved the first and last rows as variables.

In 1.2.5. I then tried to read the range of the LastRow. But, now I see that I ofcourse have to specify which cells in that last row I want to copy as well. How would I be able to do this?

[ashokkarale]

@Youri98,

Are you able to get Last row?
If yes, use Read Cell activity to read the last row cell which will be your total value.

[Youri98]

How do I do that? I would have to use the variable of last row into the Read Cell Value?

[pratik.santani]

In the range field which is showing the error mark, you have to give the range not just the row number which was found in 1.2.4 as shown in the example below : “Tabelle1” is the sheet name and you can change the A to AE range as your needs.

[Youri98]

Thank you for trying to help. What would I be doing wrong here?

o
Sorry for the probably ‘dumb’ question. Finding the dynamic ranges quite difficult.

[ashokkarale]

@Youri98,

Pass it like Pratik shared but in Read Cell -->Cell property.

Thanks,
Ashok

[Youri98]

I get the same error, am I referring to the sheet correctly or do I need to refer to the pivot table directly?

[ashokkarale]

@Youri98,

Ok here is sample code to refer.

I’m assuming you have file like this.

Code like this.

Output:

Sample code:
PivotDemo.zip (143.5 KB)

Thanks,
Ashok

[ashokkarale]

@Youri98,

My bad, I just realized last row is not really last row. We will have add 1 to last row to get total like this.

Do let me know if any issue.

[Youri98]

Thanks for the help. I’m going to try and rebuild this Will let you know soon

[ashokkarale]

[Youri98]

Took me some time :D, but I got it working thanks to you. Appreciate your help a lot! I wasn’t thinking clear, was using a range formula in Read Cell… Obviously not working when I look at it now. Thank you!

[ashokkarale]

@Youri98,

Glad to hear that Happy automation

Thanks,
Ashok