Print Macro Help

[davespunk]

Hi,

I have created an Excel workbook that shows performance of a sales
force by the use of a graph and a drop down list (so that I don't need
to create a new worksheet for every employee). The drop down list is
in cell B5 and the worksheet is called 'Sales'.

I have been asked to create a print macro that will print out the
graph for each employee but I don't know how to go about creating a
print macro that can cycle through the drop down list and print off
the 7 performance graphs (for the 7 employees in the list).

I have previously hacked together a print macro way back in Excel 2000
which cycled through different worksheets printing each one in turn
but I have no idea how to go about printing different graphs sourced
from a single worksheet. Can anyone help?

DS

 

[Dave Peterson]

If you created the dropdown using data|validation and a source range, you could
cycle through that range.

Dim myRng as range
dim myCell as range

set myrng = worksheets("SheetWithListNameHere").range("listnamehere")

for each mycell in myrng.cells
worksheets("sheetwithdropdown").range("a1").value = mycell.value
application.calculate 'just to make sure
'print stuff here
next mycell

I'd record a macro when I printed what I wanted. Then I could merge it into
this shell.

You'll have to change the names of the worksheets (both of them) and the address
of the cell with the validation and the name of the list.