Dropdown menu (printing issue)

**lolla70** · 08-14-2007, 03:43 PM

Hi
I have a worksheet that have a dropdown menu with more than 60 names / project when I select a name / project all my sheet update per the project. My question is how to run a macro to print all records in the drop menu one time instead of doing it sheet by sheet!!!! and I want to print them pdf.

Thanks for help

**royUK** · 08-15-2007, 02:47 AM

What can of drop down are you using?

**lolla70** · 08-15-2007, 01:06 PM

I did it through Data Validation / List and I add the source. Is that what you meant??

Thanks for your help

**royUK** · 08-15-2007, 01:19 PM

Yes, you could simply write some code to loop through the source list & change the data validation cell, then print.

**lolla70** · 08-15-2007, 02:12 PM

ok how I will do this!! Can you help me.

Thanks again

**royUK** · 08-15-2007, 02:34 PM

I need to know the relevant ranges, eg the data validation Range. Attach an example workbook.

**lolla70** · 08-15-2007, 10:25 PM

I delete a lot of data trying to make the size file 100K so I can upload it.

Thanks for your help!!

**royUK** · 08-16-2007, 02:33 AM

What's the point of uploaading a workbook that has the VBA project protected?

Which cell contains the Data validation & where's the list?

The sheet is full of Ref errors

**lolla70** · 08-16-2007, 08:13 AM

I am sorry for that. I didn't mean too.

My file is too large that why I delete a lot of data and the why most of the cells contain Ref. and errors.

The List is in the Master sheet Column A "ProjectList" and the data validation drop menu is in the PSR Column J&K Row # 3.

Sorry again and thanks

**royUK** · 08-18-2007, 07:02 AM

Please don't cross post without letting us know

http://www.mrexcel.com/board2/viewto...389719#1389719

Your workbook isn't very well designed, so you are limited to a fixed range

Please Login or Register  to view this content.

**lolla70** · 08-18-2007, 08:41 PM

Sorry that I post it in another place but I was in hurry and I was in need to the solution. I thought you are not going to answer after I send the first time my file with password by mistake. I felt that you are angry.

I am new to VBA but at least I am trying to learn.

I will copy the solution will try it

Thanks for you help

**lolla70** · 08-30-2007, 10:51 AM

Hi again

My problem is solved but I have another problem. Everytime I print using this macro I need to save and rename the sheet so I have about 54 records. How can I run the macro saving all the records in one pdf file instead of doing it file by file and change name file everytime.

Thanks
Leila

Option Explicit

Sub listAll()
Dim cl As Range
Dim rng As Range

Set rng = Worksheets("Master").Range("a7:a60")
For Each cl In rng
Worksheets("P S R").Range("j3").Value = cl.Value
Application.ActivePrinter = "Adobe PDF on Ne09:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne09:", Collate:=True
Next cl
End Sub

**MartPoom** · 09-24-2012, 03:09 PM

Hi Lolla70, I am having a similar issue, whereby I want to save each tab automatically (ideally with the name in the drop-down list). Do you resolve this issue?

Thanks
Mart

**arlu1201** · 09-25-2012, 04:36 AM

MartPoom,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.