+ Reply to Thread
Results 1 to 14 of 14

Dropdown menu (printing issue)

  1. #1
    Registered User
    Join Date
    08-08-2007
    Posts
    23

    Dropdown menu (printing issue)

    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

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    What can of drop down are you using?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    08-08-2007
    Posts
    23
    I did it through Data Validation / List and I add the source. Is that what you meant??

    Thanks for your help

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    Yes, you could simply write some code to loop through the source list & change the data validation cell, then print.

  5. #5
    Registered User
    Join Date
    08-08-2007
    Posts
    23
    ok how I will do this!! Can you help me.

    Thanks again

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    I need to know the relevant ranges, eg the data validation Range. Attach an example workbook.

  7. #7
    Registered User
    Join Date
    08-08-2007
    Posts
    23
    I delete a lot of data trying to make the size file 100K so I can upload it.

    Thanks for your help!!
    Attached Files Attached Files

  8. #8
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    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

  9. #9
    Registered User
    Join Date
    08-08-2007
    Posts
    23
    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
    Attached Files Attached Files

  10. #10
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    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.

  11. #11
    Registered User
    Join Date
    08-08-2007
    Posts
    23
    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

  12. #12
    Registered User
    Join Date
    08-08-2007
    Posts
    23
    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

  13. #13
    Registered User
    Join Date
    09-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Dropdown menu (printing issue)

    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

  14. #14
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Dropdown menu (printing issue)

    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.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1