Macro for printing from a drop down box

[Tor!]

Hi
I have created a drop down box from a validation list and need some help on the macro in order to automatically print the file details for each item in the drop down list.

Please can you advise how to do this - I am pretty new to macros.

Many thanks
Tor

 

[Smitty]

Welcome to the Board!

If you record a macro printing a few of the conditions you have, then post it here along with an explanation of how the conditions relate to your validation, we can help you set something up.

 

[Tor!]

This is the macro I've just done, I selected 3 different items from the drop down box - this is the bit I need it doing automatically. My list is in cell D6.

Thanks for your help

Sub Printer_macro()
'
' Printer_macro Macro
' Macro recorded 15/03/2011 by Victoria Lourensz
'
'
Range("A1:J118").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$J$118"
ActiveWindow.SmallScroll Down:=-3
Range("D6").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

 

[Smitty]

OK, a few questions:

1) The Data Validation is in D6, but where's the source?
2) Assuming that the Print Area changes for each selection, what are they? (E.G. Selection 1 = Range("A1:J118")).

Have you thought of setting up some Custom Views? If so this may be helpful: http://support.microsoft.com/kb/291072

 

[Tor!]

It is linked to the next worksheet I've created a data range. The print range will be the same regardless of what is selected from the drop down box.

Thanks for the link - this website is blocked by work though!

 

[Smitty]

OK, so now I'm assuming that your list selections drive some changes on the sheet. If that's the case then you can loop through each selection, print and move on to the next. Something like this:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> PrintfromDVList()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> ws = Sheets("Sheet2")<br>    <br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> ws.Cells(Rows.Count, "A").End(xlUp).Row<br>            <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1")<br>                .Range("D6").Value = ws.Cells(i, "A").Value<br>                .PrintPreview <SPAN style="color:#007F00">' Change to PrintOut after testing</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Where Sheet2 is the Data Validation source sheet and the list is in Column A with no headers, and Sheet1 is the sheet to print.

 

[Tor!]

Thanks very much - wil save me sometime!