How to automate save / print from drop down list vba dynamically updated data

[maku]

I am coming to the end of a long project I have working now. It is an excel database of 80 plus housing properties. Each one of the 80 plus housing properties has it's own worksheet tab. In addition there are worksheet tabs with pivot tables, pivot charts and a report card summary sheet.


I view the data by going to the report card summary sheet. When I pick from various drop down lists (year, quarter, and property name) everything dynamically updates from pulling data from each of the individual property sheets, the pivot table and pivot chart. It takes my new PC about 3-5 seconds to populate and update all the data for that property. I can then print or save that page. Then I pick the next one in the list. I would like to automate this process of picking each property in the drop down list individually to having VBA do it.


My questions are: 1) will VBA wait the 3-5 seconds to correctly re-populate all the fields and charts on my report card summary sheet before moving on to the next property? 2) What is the best method to have VBA pick from the drop down list then save that worksheet as a pdf (property name and date)?


I've read through some forums and this may be in the right direction:? http://chandoo.org/forums/topic/macro-to-print-out-different-dashboards-from-a-list-of-names


I've also read chandoo's post about split / extract data: http://chandoo.org/wp/2011/10/19/split-excel-file-into-many/ but I don't think this is what I'm needing, as I just need to pick from the drop down list, let the data populate and then save, then pick the next item in the drop down list.


Thank you for any ideas to head in the right direction. Thanks! -Maku

 

[Luke M]

Hi Maku!


A couple of "pieces" of code that we need. First, we need some sort of loop to get all the names. I'm assuming you're using INDIRECT functions to get the data populated? Assuming the data is getting populated either that way, or by another macro, you should be okay.


Next, do you have a list somewhere of all the names? Assuming so, your loop then will be something like this:

[pre]

Sub PsuedoCode()
Dim xName As String
Dim c As Range
Dim MyList As Range
Dim DropRange As Range

'Where is list of names?
Set MyList = Worksheets("Sheet1").Range("A1:A100")
'Where is dropdown?
Set DropRange = Worksheets("Sheet1").Range("b1")

Application.ScreenUpdating = False
For Each c In MyList
xName = c.Value
DropRange.Value = xName

'workbook should automatically calculate/run macro
'at this point

'TO DO:
'Record a macro of you printing to a pdf
'should look something like:
Application.ActivePrinter = "Adobe PDF on Ne02:"
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,""Adobe PDF on Ne02:"",,TRUE,,FALSE)"
'or, if you see where it record the file name, you can use the
'xName variable to populate the name
Next
Application.ScreenUpdating = True
End Sub

[/pre]
There's a lot still to fill in, but hopefully you can figure it out, or post back where when you get stuck.

 

[maku]

Well...this is a start...which is farther than I was It didn't quite work. The data is pulled mostly from the report card sheet I'm trying to pull via INDIRECT. But, some data goes to a parameters page, which identifies which list should be made and that uses VLOOKUP.


I do have a list. I tried the macro and it seems to possibly be going down the list, but each pdf created doesn't bring in any data...it shows ### or #NA and the pivot charts don't change.


Is the where is the dropdown meaning what cell is the dropdown tied to on my report card sheet? or what the objectname is for the dropdown? Thanks, -Maku

 

[maku]

Luke...I changed the droprange and tied it to the sheet that ultimately selects the property from the list. (there are a couple places use that, and some use VLOOKUP to pick that number to the corresponding name).


Anyways, by changing that, it did change the name and half the data on the report card summary sheet and correctly went down the list and printed/saved a page for each property.


It still isn't "waiting" for all the pivot table data and pivot charts to dynamically change based on that property selection though. It just prints out the heading, but all the "data" remains the same.


Also, I see a another problem. Some "lists" are longer than others depending on what state is selected. So, if I pick the range of the longer list, when I am printing a shorter list, it prints out extra blank pages for each "blank" item in the list range.


THanks for getting me going. I'll keep at it. -Maku