Consulting

Results 1 to 2 of 2

Thread: Automation of Drop Down list

  1. #1
    VBAX Newbie
    Joined
    Apr 2018
    Posts
    1
    Location

    Automation of Drop Down list

    I have a dropdown in one of my sheets, selecting any one item in the dropdown updates 4 sheets and then the data in 4 sheets are converted into PDF.
    I am facing problem in automating this step. I am able to generate PDF through Macro but in the drop-down list. I am not able to automate selection of entries.

    Sub Test6()
    '
    ' Test6 Macro
    '
    ' Keyboard Shortcut: Ctrl+k
    '
    Range("B4:C4").Select
    Application.Run "'Copy of North TSC Format (2).xlsm'!PDFActiveSheet"
    Sheets("Talent Scorecard - CV Passenger").Select
    Application.Run "'Copy of North TSC Format (2).xlsm'!PDFActiveSheet"
    Sheets("Talent Scorecard - SCV Cargo").Select
    Application.Run "'Copy of North TSC Format (2).xlsm'!PDFActiveSheet"
    Sheets("Talent Scorecard - MHCV").Select
    Application.Run "'Copy of North TSC Format (2).xlsm'!PDFActiveSheet"

    I want this to repeat for other entries in the drop-down list.

    Can anyone help me in this?

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    In the code, between each run of PDFActiveSheet, you neeed to change the value of the dropdown cell.
    Let's say that cell is B2:
    Sheets("the name of the sheet that cell B2 is on here").range("B2").value = "something"
    Application.Run "'Copy of North TSC Format (2).xlsm'!PDFActiveSheet"
    Sheets("Talent Scorecard - CV Passenger").Select
    Sheets("the name of the sheet that cell B2 is on here").range("B2").value = "something else"
    Application.Run "'Copy of North TSC Format (2).xlsm'!PDFActiveSheet"
    …etc.
    changing what obviously needs changing in the code above.

    If you know what the something and something else are you can write them into the code.
    If they're liable to change, you can go to the source range of the data validation and iterate through those cells' values.

    We'd need a bit more detail.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

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