How do I add last line of pivot table to this forumla?

Hi

I have this forumla which is copying data from one sheet to another. The data I'm copying is total revenue for a pivot table but depending on the filters you select the length of the pivot table changes depending on what data it is showing. I want to replace Range("B73:B73") with the last line of pivot table.

Please Login or Register  to view this content.

The data in the pivot table ends in cell B72 on the Revenue Dashboard sheet but this needs to change depending on where the pivot table ends, depending on what data is showing.

How do I copy just the last line of the pivot table?

Thanks,

Thanks, what would my forumla look like with this included then?

I can't work out where to insert it into mine. Could you show me with a quick copy and paste onto my original?

Thanks,

Please Login or Register  to view this content.

Sorry! Like this?

Thanks a lot! One problem though, on the revenue dashboard sheet, I actually have 2 pivot tables and the code you've provided me with is displaying the total for the wrong one. The one I want is in columns A&B lines 1-73 with the grand total being on line 73. How can I select the total from this one?

Thanks

Please Login or Register  to view this content.

My Pivot Table is called 'PivotTable6' I've put a 6 in where you suggested but I get a 'runtime error 9' Subscript out of range - when I press play macro.... any thoughts?

Thanks, I've changed it to the above but it's still giving this error: runtime error 9' Subscript out of range

Please Login or Register  to view this content.

Got it now, thanks for your help so far. I want the data to be pasted in cell T63 in Overall Dashboard - what do I change in the code to achieve this?

That's brilliant! thanks. I need to attach this info to 'CommandButton1' which I have on my sheet. When I click the button I want the data to be activated. So every time I click on the button, the data appears. How can I code the button to do this? Currently, the only way to get the data to appear is to press the triangle (play button) in visual basic and I need a button on the sheet.

Thanks

Cool, thanks, final question:

I want the code to be pasted in cell T63 not down from A1 as it is. How can I achieve this? Please show me where to put T63 into the code below.

Please Login or Register  to view this content.

Thank you

Sorry this is the code:

Please Login or Register  to view this content.

I want the data to appear in cell T63 when I click on command button one and then in the bottom half I want to clear cells T63-T70 when I click command button 2

How do I modify it to get the results I want?

At the moment, the total is being pasted down line by line from A1 but I want this to be from T63 instead.

It dosent work... I get a runtime error

OK I'll explain from the start:

This is the code I currently have:

Please Login or Register  to view this content.

What this does is copy the grand total from my Pivot Table 6 in Revenue Dashboard to Overall Dashboard. This is currently being pasted in cell A1 (maybe my default) but I want it in cell T63 instead.

I've replaced the this code with what you suggested further up the thread but I get an error when I press play.

I'm assuming that there should be an easy way to tell the code to paste the data in cell T63. I'd like to find out how... Could you please tell me or show me what the code should be.

I can clarify further if you like, thanks for your help so far, I'm almost there!

Thanks but could you show me where in my code in post 23 it goes. Everytime I add in the line

Please Login or Register  to view this content.

to what I already have, I get errors when I press play.

I've tried replacing existing code in there and adding it to the bottom but it's not working...

OK it's looking like this:

Please Login or Register  to view this content.

And when I press Play, I get a Run Time Error '9' Subscript out of range error...

Any thoughts on how to solve it?

Please Login or Register  to view this content.

This provides a runtime error '1004' Unable to get Pivot Tables property of the worksheet class.

When I click debug the top line is highlighted yellow...

bizarrely it has now started pasting in T63 but it is not adding lines beneath it as it was previously. I want to change the slicers which in turn change the data that is being copied. So I need the same 'data being added on lines beneath cell T63' feature. Also as things stand the slicers freeze after I press the button a few times. I can't share the workbook as it contains analytics about the company I'm working with which is private

I feel I need to add this row

Please Login or Register  to view this content.

but where in the code does it go?

That code provides a Run Time Error '1004' Application defined error

Any ideas why?

Thanks

No it's not... any ideas? We're almost there!

Please Login or Register  to view this content.

There appears to be a problem on the first line as when I press debug it comes up yellow... does this help?

Please Login or Register  to view this content.

This is the code I have and feel I should use as it means that the data will be copied and pasted into cell T63 and one below that every time I press the button , so I can easily compare numbers drawn from the table. This code produces a 'Run Time Error '1004' Application defined error' when I press play and says theres a problem with the top line (in bold). The table is called Pivot Table 6 and this line is the same as I was using in post 23 when the code was working fine - apart from it posted the data into cell A1...

Sorry edited it to what it actually is in Visual Basic at the moment. If that's not correct please show me how it should be. The code in post 42 is causing the runtime error.

Still same run time error with that....

Please Login or Register  to view this content.

It says it can't get the pivot table properties of the work class sheet

Ok I've written over the pivot table 6 line and it seems to be working now.

But it's still pasting values in T63 and going over them.

I want it to paste the first value in T63 then the next time you click T64 then the time after T65

So data ends up like this when you click 3 times:

404.9
22.5
335.5

One under each other

Any ideas?

Cracking, it's working! Very last question:

Please Login or Register  to view this content.

With the range here I want it to lear from R63 to T68 (essentially clear everything within this rectangle section) How would I modify the range to get it to clear down from R63 to R68 and T63 to T68?

Thanks a lot for your help, I really appreciate it. You've helped me build a cool tool at work to make it really easy to analyse and compare data from app analytics

Yeah that works! I've got all my tables set up for all pivot tables on my dashboard but after a few times of pressing my 'Compare' and 'Clear' buttons they stop revealing data and load blanks. What would be causing this? The code is fine as it was doing it before fine, but after a few times it stops. I've tried 'resetting' and pressing 'play' in VB but that's not having an effect... any ideas. I'll post the code if you like but I don't think it's that as there are no errors.

So I click the button and earlier it was loading the number that I wanted however now when I click you can see Excel 'load' and work out the function but no numbers appear and the cell is just left blank...

Theres a brief delay while it works out what it's doing the 'loading' symbol comes up on the cursor. Basically I have some 30,000 lines which Excel is summarising into graphs, pivot tables and totals and it takes a brief while to calculate what it's doing. Its a minor point I think, the main problem is that it's not showing the data after a few clicks of the button. It works for the first few times then dosent and the code hasn't changed

It does indirectly. Anyway, ignore the 'loading' side I was just trying to describe the problem.

Why would a code stop working suddenly when nothing has changed? Any solutions to what I'm experiencing?

This is all the code I have:

Please Login or Register  to view this content.

Does anything appear to be interfering with each other?

This is code for a series of compare buttons for different sets of data and each one has a 'clear data' button. At the end I have a 'clear slicer' button at the end.

I've corrected it now above... it's because I'm copying it from my PC to my mac which I'm using to post on here. Everytime i put the code in an email it adds a smiley face instead of the x and i miss it so it ends up in here. If you see it, assume it's Max(.Cells

Any ideas?

Please Login or Register  to view this content.

Should this row be the where I'm pasting to or where the data is being copied from?

Please Login or Register  to view this content.

This is the code I'm currently using and it's pasting data in cell T195 not T63... any ideas why? I've deleted the rest to try to isolate the problem.

Please Login or Register  to view this content.

That's the full code, only command button 5 is doing what it should and pasting data into the right cells. The others above aren't... any ideas? I have no idea

Please Login or Register  to view this content.

That's the full code, only command button 5 is doing what it should and pasting data into the right cells. The others above aren't... any ideas? I have no idea

I have 4 different sheets: overall dashboard, revenue dashboard, impressions dashboard and clicks dashboard

Each of the Revenue, impressions and Clicks sheets contains a pivot table and pivot graph which are copied and put onto the overall dashboard to give a nice clean view of data.

Command Button 1 is supposed to copy the grand total of the Pivot Table on revenue dashboard to cell T63 and paste downwards in 'overall dashboard'. It was doing this before as I said but has now stopped working.

The same applies for 'Command Button 3'

Only Command Button 5 is pasting data from impressions dashboard pivot table total into cell S197 and down in overall dashboard.

I've copied the code from Command Button 5 and applied it to Command button 1 but it's still not working...

Any ideas?

I have 4 different sheets: overall dashboard, revenue dashboard, impressions dashboard and clicks dashboard

Each of the Revenue, impressions and Clicks sheets contains a pivot table and pivot graph which are copied and put onto the overall dashboard to give a nice clean view of data.

Command Button 1 is supposed to copy the grand total of the Pivot Table on revenue dashboard to cell T63 and paste downwards in 'overall dashboard'. It was doing this before as I said but has now stopped working.

The same applies for 'Command Button 3'

Only Command Button 5 is pasting data from impressions dashboard pivot table total into cell S197 and down in overall dashboard.

I've copied the code from Command Button 5 and applied it to Command button 1 but it's still not working...

Any ideas?

OK I think I've managed to figure it out myself. One more quick question:

Is there anyway to adapt this code:

Please Login or Register  to view this content.

To clear contents in Active X text boxes as well as normal cells? The text boxes lie in the Q column and I want them to be cleared as well, whenever I press 'clear' (command Button 2)

Thanks

That's fantastic! I've combined the previous code with that one there to clear all textboxes and normal columns.

One more thing actually,

Please Login or Register  to view this content.

Is there a way to get this to stop after line 70? So it will copy for 7 clicks only before you have to use the 'clear' function to do it again? My concern is that someone could just keep pressing my Command Button 1 (compare button) to have data being pasted all the way doen the page.

Thanks for your help, I really appreciate it!

That worked thanks. I've established the problem I've been having too and it's come back again unfortunately. It only seems to want to post in each column once before giving up. So I've ran the code above in column P and it worked fine once but when I try to do it a second time, it refuses to load anything and instead the 'please press clear then run this code again message pops up' without the numbers first.

I have moved the pasting destination around columns and it does it once before not working anymore. How can I get it to keep pasting on demand in the same column?

Thanks

I've added that to the end of the code in VB as it is above but I'm getting a Compile Error. I'm assuming theres more to do than just sticking it at the end of the code, I am a beginner.

I think you're right as code is being pasted way down in cell P136 when I click the button Command Button 1 in post 79... Do you know how to fix this? Again I've deleted the rest of the code so I'm just working with what's in post 79 but I cant see where it's getting the instruction to post in cell P136 from...