Macro to change validation list and print


I have a worksheet with graphs and tables on it which are all based on a 
validation list (the drop down menu variety) with about a dozen categories. 
Obviously when i change the selected category, all the graphs and tables 
change too.  

I would like to create a macro that will print one copy of the worksheet for 
each category on the validation list, rather than having to manually change 
the list and then print the worksheet a dozen times.

Any suggestions?

Andy1200 (318)
9/19/2005 4:55:01 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 22

Did you put that list of 12 on a worksheet or did you enter it in the
data|validation dialog directly?

If you put it in a list on a worksheet, then something like this could work:

Option Explicit
Sub testme()

    Dim myCellWithValidation As Range
    Dim myValidationRng As Range
    Dim myCell As Range
    Set myCellWithValidation = Worksheets("sheet1").Range("a1")
    Set myValidationRng = Worksheets("sheet2").Range("myList")
    For Each myCell In myValidationRng.Cells
        myCellWithValidation.Value = myCell.Value
        myCellWithValidation.Parent.PrintOut preview:=True
    Next myCell
End Sub

I used preview:=true to save paper.

Andy wrote:
> Hi,
> I have a worksheet with graphs and tables on it which are all based on a
> validation list (the drop down menu variety) with about a dozen categories.
> Obviously when i change the selected category, all the graphs and tables
> change too.
> I would like to create a macro that will print one copy of the worksheet for
> each category on the validation list, rather than having to manually change
> the list and then print the worksheet a dozen times.
> Any suggestions?
> Andy


Dave Peterson
petersod (12005)
9/19/2005 12:42:12 PM

Similar Artilces:

Macro help #12
Hi, I am working on a macro in Excel 2003 Pro, and everytime I go to sort a column of data. There are about 10 rows of data in about 200+ columns. I can only get the macro to sort the column that I edited it with. How can I create the macro, so each time I click on the first row of each column, click the macro button I will assign to the toolbar, then that row will be sorted, and do this for each column? Any help at all would be greatly appreciated, Jeff Garrett ( Hi Jeff please post your current code and then we can suggest changes to it. Cheers JulieD "...

Excel 2007 Need to permanently change Normal settings for gridlines
I am using Excel 2007 for the first time and find the gridlines delineating the cells are so faint as to be nearly indistinguishable. I can go into the cell formatting and modify the normal style, and it is just the way I want it. But I can't find a way to make Excel remember this and treat it as the new definition of the Normal style. I don't want to have to redefine Normal every time I open a new spreadsheet. Someone please help! Regards Leonard Priestley The changes you describe are changing the Border color and NOT gridlines. Go into Excel Options and cl...

Message "class not registered" opening sheet with macros Excel 97
I have created a document with Macros and is password protected. I have sent it to several people who are able to use it - the one who can't is using Excel 97 SP2. They are getting error message "class not registered" - could it be the version they are using and how do I make sure they are able to use it. This could simply be a version issue if you developed on a later version and used controls from that versions object libraries. You should always use the lowest version to develop on. If this is not the case look in the VBE on the faulty machine and check tools>ref...

Publisher 2002 print issues
We have created a reference manual. What we see in the working document is not what goes to print. Some pages are fine while other pages have shifted pushing the title of page to the next page. Is there a way to fix the document in print preview mode? It appears to be an impossible problem! Please help if you can. thank you. There is no Print Preview mode. What you need to do is increase the size of the Text Frame fractionally. The easiest way to do this is by changing the Text Frame margins. Format Text Box > Format Margins. Publisher 2002 was a dog of a product in the area...

Printing problem from Excel 2007
Greetings, I am having an issue with printing from Excel 2007. Many users when printing from Excel with get several pages of non-sensical "junk" printed out on the pages. If it actually prints what is on the page, none of the items are in the cells, but look like they are just randomly thrown about the page. I have found that this most often is related to the Calibri font somehow, because I instruct them to change the font to Arial and all prints fine. That being said, if the user converts the Excel sheet into a PDF and print, it prints perfect. This is a great workaround, but kind ...

Changing which database Business Portal points to
If I install Business Portal 2.5 (GP) and point it to our Great Plains development server with an old Dynamics database, once we decide to take it live, how do I go back and point it to the live Great Plains server? Is this something that can only be done during the installation? Let me preface my response by saying I only have experience with BP 3.0.... In BP 3.0 several tables are created in the Dynamics database to support the various BP functions. Unless you plan to move or copy those tables you will probably have to reinstall BP 2.5 in order to have them created. Our attempt to cop...

Printing as PDF
I have a portion of a sheet that I want to print/save as a pdf to a folder on my hard drive. The folders location is C:\Documents and Settings\Compaq_Owner\Desktop\Invoices. I also want the file name to be the value found in cell j19 on the "PO Form" worksheet. Any help? Hi Download and install Cute PDF-Writer ( It installs as a printer. Now, you can write any documents (Excel, Word, various pictures, web pages, etc. to PDF format. Simply open the document, select Print from menu, select CutePDF Printer, and when you click on Print button, a file save dia...

Macros #50
How do I move macros from one computer to another? In the VBA editor, right click on the sheet name or module name (in the left panel)that contains the macro and click on "Export File" -- Regards, Dave "Neal" wrote: > How do I move macros from one computer to another? Macros are always stored in workbooks, so you can simply copy the workbook containing the macros from one machine to another. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC "Neal" <> wrote in mess...

Problems printing in Publisher 2003 with HP PSC 2410 #2
I am new to publisher 2003 and am having great difficulties printing anything properly using an HP PSC 2410 all in one. As an example when trying to print a half page side folded pre-designed greetings card all that prints is page 1 and part of page 4, minus any text. Pages 2 and 3 are missing altogether. The print preview however displays everything correctly. I have reinstalled Office 2003 and updated the printer drivers to the latest versions but the problem remains. Any help is much appreciated. ...

SMTP address list
Does anyone know of a way to obtain a list of all SMTP email addresses accepted by a server and the corresponding user account in Exchange 2003? Thanks Brad Palmer You can use LDIFDE to dump Active Directory attributes to a text file. The email addresses are held in the 'proxyAddresses' attribute, so: ldifde -f dump.ldf -l proxyaddresses This will give you a file called dump.ldf containing your addresses. You can add other Active Directory attributes to the export file as you see fit. -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexc...

Changing Titles by a formula
From cell A1:A10 I have standard titles that are linked to another sheet. I only need to change the office and date per each title. Is thier a way to do that making the office and date a formula in another cell? example of title ABC Comp, Office 38L Sales as of 6/07/2004 I need to only keep changeing the 38L and date. Any suggestions----Thanks Say Office # is in Column 'C' in cell A1: ="ABC Comp, Office "&C1&" Sales as of "&Today() ----- Heather wrote: ----- From cell A1:A10 I have standard titles that are linked to ...

Print Macro #2
Trying to write a macro to print a spreadsheet where the number of rows of data varies. What's wrong with this code? Dim rng As Range Set rng = Range("A1").End(xlDown).End(xlToRight) With ActiveSheet.PageSetup .PrintArea = rng End With Thank you. Pam Hi Pam Leave your PrintArea empty Excel will print all cells with a value on the sheet -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) "Pam" <> wrote in message news:16e401c38521$a7d25420$3501280a@phx.gbl... > Trying to write a macro to ...

problem in changing the text of sentences before tables
I am developing a word automation application. In a method of mine, I change the text of some sentences of an opened word file, but the problem is when I change the text of a sentence which located before a table, it will be moved to the first cell of the table. My code is as follow: void myMethod( long startingSentenceNumber, const char *toBeSearched, const char *replacement, bool replace ) { Range currentSentenceRange; Selection sentenceSelection; Sentences sentencesList = m_document.GetSentences(); long sentencesCount = sentencesList.GetCount(); CString replacementCStr(...

Printing Labels on Sato CL408
We are in the process of migrating from our existing proprietary POS and Inventory system to RMS. Part of the migration involves migrating the label printing function for our existing Sato CL408 label printers. I have done this successfully except for one detail. The labels are small labels (1 in. by 1.5 in) that are printed side-by-side. To get the second side-by-side label to print, I have to "fool" the label designer wizard into thinking it is printing to a label 3.5 inches wide. The data on the left side is duplicated on the right side, resulting in two labels being p...

Using paste in macro but with variable references.
Hi all, I am trying to use the paste function in a macro but it needs to be variable. I have split a list of data (team workloads) into the seperate teams and by days of the week. I now need the macro to compile one book with all the data in order. The headings for each team are already set on a worksheet so the data needs to be entered under each heading For example: Team A Day 1 under the heading "Team A Day 1", Team B Day 1 under the next heading on the same sheet "Team B Day 1". I hit problems as the workload ammount will alter from week to week. So of course asking...

Textbox's truncated once printed (Excel 2000)
When using text boxs in excel if the data entry is larger than the text box it does not print out everything in the text box. Is there a way to get it to print out all the information contained in the text box. For the only two solutions I can think of is 1) Make the text boxs bigger but my manager rejected the idea. 2)Cut an paste the information onto a excel spreedsheet then print it that way by using a macro but that is a last resort. Dear Robert, 1) 2) and 3) Reject your manager. If it doesn't fit, it doesn't. 4) Perhaps ... use a smaller font? 5) Refrase your text ... Fran...

can't change/delete bills and deposits
suddenly one day i realized that when i tried to change a bill, i could click "ok" but nothing would happen. I thought it was because i upgraded to 2004, so i deleted the bills and rescheduled. about 5 of the bills will not delete either. the error message is: "Money could not write to your Money file. The operation could not be performed or another application finished this task before you. Please try again." Now some of the newly scheduled bills will not allow me to change the properties, i click ok but nothing happens when i edit the series. I have repaired ...

Outlook 2007: When i Save a New Rule outlook Change it automatically...
Hi people! I have this problem with Outlook 2007. I have 15 e-mails addresses in my outlook, but i can=B4t create the rules for send all the received mails to each emails folder. Example: I want to create a simple rule that move all the e-mail that i get from "" to the folder "Inbox/". When i create the rule, all works fine, i select the correct e-mail account name, the correct destination folder, etc... but when I hit the SAVE button in rules window, Outlook 2007 changes the account automatically to another one. Not automatically...

Change Row/Column Height & Width
I know I should be able to automatically set a row height to the max necessary by hovering the cursor between the 2 rows I want to adjust and double-clicking, but sometimes this doesn't always work. Why is that that - do I need to adjust a setting? And is there any way to set it so that if text is added or deleted the row height would change automatically so thatthe text fit appropriately? Set the row format to Autofit and cells to Wrap Text Gord Dibben MS Excel MVP On Tue, 19 May 2009 12:14:04 -0700, DaveL <> wrote: >I know I should be ...

Changing Functional Currency #4
Try to change the functional currency to Z-CNY. ERROR = Activity for this currency already exist. Finance made 2 GL transactions. Can we remove the transactions (Tools >> Utilities >> Financial >> Remove History) or (File >> Maintenance >> Clear Data ) then change the functional currency? Which tables should I remove history or clear data. Can someone on the board help us? regards, Once you process a transaction, you are stuck with the functional currency. I have not seen a table fix for this problem on the board. Consider creating a new database an...

Adding Members- Dist. Lists
Not sure if I should be posting this here or in the SBS group. Does anyone know if there is a way to assign a permission to a user in our office to be able to update membership lists for distribution groups while she is in Outlook? Currently, when she clicks the "To:" button in a new email, right-clicks the distribution group, selects properties, goes to "modify members", it lets her select the new member but then when she clicks ok she will get an error mssg saying she doesn't have permission to do this. thx! on the security tab of the DL, does she have the &qu...

Sheet display vrs. Print Preview
Why would a sheet display differently than when it is printed. I have an excel file that looks like it prints on my bosses computer but on my pc the page breaks are different the word warps are different. This makes it difficult to work with because it will not print the same on my pc and his. We are both using Excel 2003. Thanks for helping. This could have a great deal to do with the printer drivers. Are you printing to the same printer? ******************* ~Anne Troy "Byron" <> wrote in mes...

Change default "SaveAs" to *.msg rather than *.html
Hi!, Using Outlook 03, I regularly need to save emails and prefer to save as *.msg so the attachments are contained within the one file. How can you change the default SaveAs command to Save as Type in a *.msg format (rather than *.html) so the type is already *.msg without having to use the pull down? Thank you Troy Outlook provides no way to change that. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers "TroyB" <>...

Output the list of frequent data
Dear all, Here comes two problems. Problem 1: I have a list of strings (say, in the column A1:A100). How can I find the "mode" (i.e. the string appearing most frequently in the list? For example, if the list is APPLE APPLE BANANA APPLE CREAM CREAM BANANA CREAM APPLE DONUT then I want the result is APPLE. It seems that the MODE function does not suppot data type other than numbers. Is there any canned UDF for it? Problem 2: Following Problem 1, I want to generate a list of the 3 most frequent data in the list. If the list is the one in the example, I want to list to be...

Excel Macro
I am writing a macro and cannot find any command that will allow me to move the cursor in the Worksheet. Rather than give a Cell address such as "A3" I want to be able to move the cursor any given number of cells to the right,left, up or down. Can anyone help? activecell.offset(x,y).select will move x rows down (if positive, up if negative) and y columns to the right (if positive, left if negative). Stephen King wrote: > > I am writing a macro and cannot find any command that will > allow me to move the cursor in the Worksheet. Rather than > give a Cell address ...