VBA Assistance

[Starlight1]

Every 2-3 days, I update a data set and subsequently update pivot tables. The pivot table I am most concerned about has nine rows and two columns. The first column contains the status of our properties (i.e., rented, rehab, turnover) and the second column contains the number of properties that fit within each particular property status. Prior to updating the pivot tables I manually copy the date onto a summary sheet that has the same property statuses in column B, and the date corresponding to the pivot run along row 2. I am trying to create a Macro to appear on the summary sheet that will do the following:

1) Update all pivot tables in workbook
2) Determine today's date
3) Locate row 2 and find the last cell with a date in it.
4) If today's date is the same as the last date entered, reenter today's date. If today's date is after the last input date, enter today's date in the next available cell
5) Lookup the value in the updated pivot tables and insert them in the appropriate cells (i.e., the number of rented homes in the cell that matches the row corresponding to rented homes and the last column with a date entry in row 2.
6) I would then like all values set so that they do not change when the macro is rerun (unless rerun on the same day, in which case the last column would be rerun.

Can anyone help?!?

 

[RickXL]

Hi and welcome to the Board!


1) There is a PivotTable option that will automatically update the table when the workbook is opened. Right-click the PivotTable and select the Data tab.
2) Today's date is given by =NOW()
3) Are all the items in row 2 dates? In which case we only need to find the last column. Or can the contents be anything so we also have to check to see if the contents are valid dates as well?
4) Not sure why you have to replace a date with the same date? "The next available cell" is that the next blank one in row 2?
5) Are you saying that you repeat the PivotTable totals somewhere else in the spreadsheet?
6) What could change to alter the data that needs to be "set". I could guess but as you know it might be easier if you tell me.

 

[Starlight1]

Hi Rick, thanks for your response.

I'm aware on the first two. My goal is to reduce the manual labor of copy and pasting data from one sheet to the next in order to be able to compare current stats v old stats. Thinking about it with one data point - homes rented. If I update my data 4x/wk, i want to be able to see the following:

6/1: 400 homes rented
6/3: 403 homes rented
6/4: 404 homes rented
6/7: 410 homes rented

In order to save that info I currently have to copy the pivot table stats and paste them on my summary sheet. The summary sheet has the data points i am trying to capture in column B and dates going across row 2. I currently CTRL : in row 2 after i paste stats.

I would like to avoid having to take all those steps and instead press a button that updates the pivots, selects the appropriate data and moves it to the summary sheet and posts the date. I was assuming that this would require in part a lookup formula on the summary sheet. If that is the case, i would need to make sure the stats hard code for past dates.

Does that make sense?