[SOLVED] Macro to Print All Options in a Drop Down List

**lianne.cuscani** · 08-06-2015, 11:09 AM

Hi There,

I have a work book here with 3 sheets:

1. Sheet one - list of Markets
2. Sheet two - Data
3. Sheet three - formatted with a range of B1:I109 that needs to be printed. Drop down list of 90 markets in C2 (linked to Sheet 1) which changes the data in the range C6:I109 based on vlookups.

I need help writing a macro that will print 90 pages, 1 for each market in the drop down with that markets data populated from the vlookup.

Could someone help me with writing a macro for this?

Thank you!

LC

**YasserKhalil** · 08-06-2015, 11:14 AM

Can you attach a sample workbook to find quick help?

**lianne.cuscani** · 08-06-2015, 11:39 AM

HI There!

Attached is an example - the number of tabs and formulas are the same, just generic data.

Thanks!

**YasserKhalil** · 08-06-2015, 11:49 AM

Try this code

Please Login or Register  to view this content.

Replace PrintPreview with PrintOut to Print all markets

**lianne.cuscani** · 08-06-2015, 12:05 PM

This only works on the example file I gave you.

I changed the market names to protect information. Each Market name is unique. For example- Market 1 in my real file would be ABC Grocery, Market 2 would be 123 Grocery, etc (those aren't the names, just trying to provide an example)

**YasserKhalil** · 08-06-2015, 12:19 PM

You can clear data validation list from C2 and put this formula in C2

Please Login or Register  to view this content.

then in G2 right click then format cells then select Custom and type

Please Login or Register  to view this content.

Now change the code to :

Please Login or Register  to view this content.

**YasserKhalil** · 08-06-2015, 12:20 PM

Another way to run this code directly without changing your file structure

Please Login or Register  to view this content.

**lianne.cuscani** · 08-06-2015, 12:53 PM

Hi! It's almost there! I used the most recent code you provided and its working except its only printing up to column F instead of column I

**lianne.cuscani** · 08-06-2015, 01:12 PM

OMG, my page layout was just wrong - that is the problem with the columns not showing.

Now my only issue is the sheet is too long so it prints 2 pages per market, but the macro print preview is just showing the first page.

**lianne.cuscani** · 08-06-2015, 01:12 PM

Everything is working - thank you for your help!!!

**YasserKhalil** · 08-06-2015, 02:15 PM

You're welcome. Thanks for the feedback and for the rep. points

**lianne.cuscani** · 04-12-2016, 06:53 PM

Hi YasserKhalil!

I was wondering if you could help me adapt this code for another spreadsheet. I tried changing the range, and worksheet names, but it will only print the first selection in a range of 3 selection. Does it have something to do with the SH or I? Thanks!

Sub PrintAll()
Dim SH As Worksheet, I As Long
Set SH = Sheets("Formated Data")
Application.ScreenUpdating = False
For I = 1 To 90
SH.Range("C2").Value = Sheets("Markets").Cells(I, 1)
SH.PrintPreview
'SH.PrintOut
Next I
Application.ScreenUpdating = True
End Sub

**unclesamshand** · 09-15-2016, 04:38 PM

Hi,
I need help on what I think is this exact same issue, but the solutions posted in the past are not loaded. I'd like to print the worksheet "Scorecard" (A1:Q39) for each of the options in C12. Any help would be appreciated!

**eileen599** · 01-25-2018, 01:51 PM

Hello! Can you help me too? I don't know ANYTHING about macros so I can't edit the one you posted for lianne.cuscani beyond the two changes in red (below).

I have 3 tabs. The dropdown is on the "Selection" tab in cell C3. It is looking at the list on "Inputs" in cells DB3:DB205. The area to print is on "Overview" in cells B2:S62.

Sub PrintAll()
Dim SH As Worksheet, I As Long
Set SH = Sheets("Formated Data")
Application.ScreenUpdating = False
For I = 1 To 90
SH.Range("C3").Value = Sheets("Inputs").Cells(I, 1)
SH.PrintPreview
'SH.PrintOut
Next I
Application.ScreenUpdating = True
End Sub

[SOLVED] Macro to Print All Options in a Drop Down List

LinkBack

Thread Tools

Rate This Thread

Display

[SOLVED] Macro to Print All Options in a Drop Down List

Re: Macro to Print All Options in a Drop Down List

Re: Macro to Print All Options in a Drop Down List

Re: Macro to Print All Options in a Drop Down List

Re: Macro to Print All Options in a Drop Down List

Re: Macro to Print All Options in a Drop Down List

Re: Macro to Print All Options in a Drop Down List

Re: Macro to Print All Options in a Drop Down List

Re: Macro to Print All Options in a Drop Down List

Re: Macro to Print All Options in a Drop Down List

Re: [SOLVED] Macro to Print All Options in a Drop Down List

Re: [SOLVED] Macro to Print All Options in a Drop Down List

Re: [SOLVED] Macro to Print All Options in a Drop Down List

Re: [SOLVED] Macro to Print All Options in a Drop Down List

Thread Information

Users Browsing this Thread

Similar Threads

Macro for drop down list options with different increases

Print all options from dropdown list to PDF and name the files the exact names in the list

Autoselect from a drop-down list and print (macro)

drop down list with options

[SOLVED] Column B drop down list with 2 options, columns C & E with dependent drop down lists

Take away options in drop-down list

[SOLVED] My Excel drop-down list eliminates from list options chosen. Help

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions