VBA to find and select Last row in a dynamic Pivot Table

I am having issues with a VBA script. The Spreadsheet is linked to a SQL DB and updated with data from scripts that are executing throughout the day. This is then fed to a dashboard. We have a Baseline that shows in the Date column as 0/0 and 1/1. I need a way to ensure that the 1/1 is always in the last row of the chart so the baseline shows across the entire range of dates. The current method is to declare a variable and add 1 to it whenever we iterate through the sort for the other dates. However everyday the 1/1 baseline column ends up further to the beginning until it is next to the 0/0 in position 2.


So I am trying to find a way to have the macros find the last row of the Date field and then set the position to be equal to the last row.

Thank you,

Paul

Hi Paul,

Can you post a sample workbook so that the Pivot Table can be seen and macros? There are a number of ways to do this!


David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi Paul,

The answer may be too simple! I changed the 0/0 and 1/1 to dates (1/1/2014 and 28/2/2014).

The chart now shows the way I imagine you want to see it! The problem is that the Baseline markers are in text while the rest are dates. The text is being sorted by the Pivot Chart to the front.

I also added a small macro called PivotOldItemsDelete. Your Pivot List was full of rubbish left over from the past. If you run this macro every time, it will ensure that the data in the dropdown is the data on the Data Sheet. Not a whole lot of other dates.

So, just anchor your baselines with dates of your choosing before and after the date renag you are dealing with.


Regards,

David


When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi David,

Thank you for your reply. I didn't think about changing the dates, I was too busy trying to make a way for the old formatting to work. Thank you for the other macros as well. I modified the SQL query that we are using on our other spreadsheet to only pull in the necessary data. I 'll experiment with your changes/recommendations.

Regards,

Paul