Need macro to print sheet with multiple repetitions

I have an excel-based sales report with charts, etc.  The territor
number in cell A1 determines the data that populates the tables an
charts.  When the user selects a new territory number in cell A1, th
tables and charts update.  

I need to print the report for every sales territory every month (5
each month.)  I don't want to have to manually select the nex
territory, print, select the next territory, print, etc.

Is there a macro that can automate the process?

:confused

--
Message posted from http://www.ExcelForum.com

0
7/23/2004 5:25:24 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
641 Views

Similar Articles

[PageSpeed] 39

Hi Bill

This sub will change the value of A1 so all cells will be update.
Print the Sheets("form") and change the value(a1)  to the next value......

>For Each cell In Sheets("data").Columns("A").SpecialCells(xlCellTypeConstants)
It will use all cells with a territory number in the A column on Sheets("data").
Enter in A1:A50 of Sheets("data") the numbers

Sub test()
Dim cell As Range
For Each cell In Sheets("data").Columns("A").SpecialCells(xlCellTypeConstants)
Sheets("form").Range("A6").Value = cell.Value
Sheets("form").PrintOut
Next
End Sub


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"bill_s1416 >" <<bill_s1416.19uxga@excelforum-nospam.com> wrote in message news:bill_s1416.19uxga@excelforum-nospam.com...
> I have an excel-based sales report with charts, etc.  The territory
> number in cell A1 determines the data that populates the tables and
> charts.  When the user selects a new territory number in cell A1, the
> tables and charts update.
>
> I need to print the report for every sales territory every month (50
> each month.)  I don't want to have to manually select the next
> territory, print, select the next territory, print, etc.
>
> Is there a macro that can automate the process?
>
> :confused:
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
rondebruin (3790)
7/23/2004 7:24:13 PM
Typo

In the sub change A6 to A1

>Sheets("form").Range("A6").Value = cell.Value


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"bill_s1416 >" <<bill_s1416.19uxga@excelforum-nospam.com> wrote in message news:bill_s1416.19uxga@excelforum-nospam.com...
> I have an excel-based sales report with charts, etc.  The territory
> number in cell A1 determines the data that populates the tables and
> charts.  When the user selects a new territory number in cell A1, the
> tables and charts update.
>
> I need to print the report for every sales territory every month (50
> each month.)  I don't want to have to manually select the next
> territory, print, select the next territory, print, etc.
>
> Is there a macro that can automate the process?
>
> :confused:
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
rondebruin (3790)
7/23/2004 7:27:05 PM
Strange I don't see my first reply
Here it is again
***************************

Hi Bill

This sub will change the value of A1 so all cells will be update.
Print the Sheets("form") and change the value(a1)  to the next value......

>For Each cell In Sheets("data").Columns("A").SpecialCells(xlCellTypeConstants)
It will use all cells with a territory number in the A column on Sheets("data").
Enter in A1:A50 of Sheets("data") the numbers

Sub test()
Dim cell As Range
For Each cell In Sheets("data").Columns("A").SpecialCells(xlCellTypeConstants)
Sheets("form").Range("A6").Value = cell.Value
Sheets("form").PrintOut
Next
End Sub



-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:O77eQsOcEHA.2880@TK2MSFTNGP12.phx.gbl...
> Typo
>
> In the sub change A6 to A1
>
> >Sheets("form").Range("A6").Value = cell.Value
>
>
> -- 
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "bill_s1416 >" <<bill_s1416.19uxga@excelforum-nospam.com> wrote in message news:bill_s1416.19uxga@excelforum-nospam.com...
> > I have an excel-based sales report with charts, etc.  The territory
> > number in cell A1 determines the data that populates the tables and
> > charts.  When the user selects a new territory number in cell A1, the
> > tables and charts update.
> >
> > I need to print the report for every sales territory every month (50
> > each month.)  I don't want to have to manually select the next
> > territory, print, select the next territory, print, etc.
> >
> > Is there a macro that can automate the process?
> >
> > :confused:
> >
> >
> > ---
> > Message posted from http://www.ExcelForum.com/
> >
>
>


0
rondebruin (3790)
7/23/2004 7:55:11 PM
Reply:

Similar Artilces:

Multiple sessions logged on
I just converted a DB to Access 2002 format. Now, every time I open it, the .ldb file shows two open sessions. Because of this, every time I open it, it won't let me edit anything or save any changes, because it thinks more than one user has the file open (No one else could possibly have it open - I just created it, so no one else even knows it exists!) Any ideas? Thanks! Joel ...

Property Sheet
Hello: How can i create property sheet without any buttons ? thank you, dave Use a tab control and child dialogs. But if you really want to use a PropertySheet, take a look at this on how to get a hold of the buttons, http://www.codeproject.com/property/hacking_the_cpropertyshee.asp at that point you can always disable and/or hide them. AliR. "dave" <dave@discussions.microsoft.com> wrote in message news:63E677A6-352E-4FE9-94EF-1ECDEE600FC1@microsoft.com... > > Hello: > > How can i create property sheet without any buttons ? > > thank you, > da...

Need invisible/shaded/highlighted cell
I'm thinking what I would like to do can *probably* be done in VBA, but I'd like to use the formula in the cell. If it can be done. I have three columns: DOW DATE BILL DTE --- -------- -------- Wed 02/01/06 02/03/06 Thu 02/02/06 02/06/06 Fri 02/03/06 02/07/06 Sat 02/04/06 02/06/06 Sun 02/05/06 02/07/06 Mon 02/06/06 02/08/06 Tue 02/07/06 02/09/06 Wed 02/08/06 02/10/06 . . . . The date col contains a formula to add 1 to the cell above. The day of week col uses the date from the date column. And the bill date col adds 4 days to the date col if it falls on a "Thu...

office prints only a half of 1st page
When I print anything in Office (excell, words..) it prints a half of first page only. help plese Use print preview to see if you have messed up your top and bottom margins or space for headers and footers. If you see nothing there try selecting all cells and clearing your manual page breaks. Insert, Reset/remove all page breaks Also check File, Print Area- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "leokon&q...

sheet protection #4
does anyone know a way around sheet protection? protection was put on a document by an ex staff member who can not remember doing it let alone the password. is there any way of resetting this? Hi Belinda Place the following code into a general module. Then select the sheet you want to unprotect and run the macro which will unprotect the active sheet. Sub UnprotectActiveSheet() Dim i As Integer, j As Integer, k As Integer Dim l As Integer, m As Integer, n As Integer Dim i1 As Integer, i2 As Integer, i3 As Integer Dim i4 As Integer, i5 As Integer, i6 As Integer On Error Resume Next For i ...

Printing Formatted Cells
I have conditional format on a cell to format with say Fill Red ... It displays OK on the screen but doesn't appear when printed? I'm sure it'll be something obvious! Is your print setting for color, or B&W? Long shot, but I'm not sure of any other reason why what-you-see is not what-you-get. -- Best Regards, Luke M "Charlie" <Charlie@discussions.microsoft.com> wrote in message news:C94E101A-9579-4FDB-8AEF-91D59E1EC3A4@microsoft.com... >I have conditional format on a cell to format with say Fill Red ... It > displays OK on the sc...

Hyperlink Macro / Hide Tabs
Hi I have an Excel 2007 worksheet with 10 tabs. The first tab has a contents section where each of the subsequent tabs is hyperlinked. By clicking on the hyperlink I go to cell A1 of whichever tab I select. In addition each tab has a hyperlink taking me to the contents tab. I would like a macro where, when I click on a hyperlink, all tabs other than the cover tab and the selected tab are hidden. In addition when I click on the contents tab in a selected tab every tab is hidden bar the contents tab. Can anybody help? Rup ...

need help... Getting a error when I add a xml schema include location xsd file
Hi, I am trying to add the following line to my xml schema xsd file <xsd:include schemaLocation="../../../Common/datatypes.xsd"/> Here is my code XmlSchemaInclude include = new XmlSchemaInclude(); include.SchemaLocation = "../../../Common/datatypes.xsd"; schema.Items.Add(include); when I print out the schema, it crashes StreamWriter sw = new StreamWriter(@strFormName+".xsd", false, Encoding.UTF8); schema.Write(sw); sw.Close(); Any help is greatly appreciated, Thanks ...

how do you print oversized to "Scale to fit"?
How do you print an oversized piece to scale down on normal paper? I cant find a "scale to fit" type option anywhere in the print dialog. I would convert it to .pdf and print that to fit. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Greg" <Greg@discussions.microsoft.com> wrote in message news:522EDEFF-50A7-4045-9434-E3F638CD35A3@microsoft.com... > How do you print an oversized piece to scale down on normal paper? I cant > find a "scale to fit" type option anywhere in the print dia...

Printing address lables from spreadsheet?
I have created numerous address lists on spreadsheets. Is it possible to construct these lists in a way to send to a printer and it just labels the envelopes and such? I do this all the time... I use mail merge in Word and use my exce sheet as the database. I have found it to be the best way to get whole bunch of different envelopes/letters out quickly -- eluehman ----------------------------------------------------------------------- eluehmann's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1309 View this thread: http://www.excelforum.com/showthread.php?th...

Macro Run on WorkBook Open?
Have a macro that I want to run when the file is first opened. Is this possible? If so, How? Thanks in advance -- / Sean the Mc / "I have not failed. I've just found 10,000 ways that won't work." - Thomas Alva Edison (1847-1931) You answered your own question. Right click on the excel icon just to the left of FILE> the workbook_open event is there. -- Don Guillett SalesAid Software donaldb@281.com "What-a-Tool" <Die!FrigginSpammersDieDie!@IHateSpam.Net> wrote in message news:MetMc.7592$BX.2445@lakeread08... > Have a macro that I want to run w...

Multiple Sheet Protection
I have over 75 sheets in a workbook that I want to protect so that the contents of the cells remain hidden. Is there a way to apply protection to multiple sheets at a single time or do I need to open each sheet and apply protection. Thanks. Hi You have to protect one by one. However, tedious repeating tasks is what macros do well, so running those two won't take half a second: Sub LockEm() Dim Wk As Worksheet For Each Wk In ActiveWorkbook.Worksheets Wk.Protect ("Harald is great") Next End Sub Sub UnLockEm() Dim Wk As Worksheet For Each Wk In ActiveWorkbook.Worksheets ...

need to retrieve recently deleted email in live mail
How can I retrieve mail that was accidentally deleted before it could be read? Open the "Deleted Items" folder under the affected account. -- Dave N. MS-MVP (Mail) Windows 7 Ultimate http://download.live.com/wlmail "manspirit1" <manspirit1@discussions.microsoft.com> wrote in message news:EA3249CE-C52E-4F47-AD4A-F633E1A06D1A@microsoft.com... > How can I retrieve mail that was accidentally deleted before it could be > read? If it is(was) a Hotmail type account(Hotmail.com, Live.com, Msn.com) using Pop3 and the message is not in your ...

OFFSET for multiple columns & combobox format
Is there a way to have an OFFSET range of two columns? I have been using multiple columns for my X axis values with non-dynamic charts. Now i'm designing a dynamic chart, but Excel will not let me select two columns (month/wk) for my X axis. Another simple question > how can I format a comboBox text in a chart? Thanks. -- when u change the way u look @ things, the things u look at change. To create an offset for multiple columns, first define a range name that makes up those columns. For example, the name "XAxisCol" covers columns A and B and refers to the formula / r...

creating a macro that will paste a value and then move
Please, help! I am trying to recreate a macro that opens a workbook, goes to Sheet 1, pastes clipboard info into A1, then moves to B1, then saves and closes the file. I know how to make the file automatic and how to get it to open and close (Auto_Open), but I cannot remember how to get the macro to move from cell to cell (left-to right). What happens is that when I do a recorded macro and then try to rerun it; it will paste into A1 and then will tab over to B1. Then upon the next time that I call up the spreadsheet, it will even paste to B1, but will not move further from that spot. I then...

Need to clear OPS drivers
Is there a way to find and remove ALL OPOS drivers - especially the ones apparently installed by pixies ( hardware vendors?) without any documentation? I just had an "experience" setting up Star TSP100 and cash drawers on RMS 1.3. Although I finally got everyting resolved (the last problem turned out to be a bad cable between the printer and the cash drawer), the headaches along the way were excaberated because I could not use the test software provided by Star Micronics. It claimed that another control object was already registered. The problem was that I had not installed an...

Need To Add VBA Tools Like A Date Picker
I have written a couple of VBA projects for my company. Most of the computers run them all fine but some do not. About half of the computers are running Windows XP Pro w/ Office XP Small Buisness and the other Half are running on Windows 2000 Pro w/ Office 2000 Small Business. My problem is that some of the office 2000 computers do not have Date Pickers in there VBA so they get errors when they try and run the programs. It says something like error: project cannot be found. I want to know where I can update my VBA tools from. Any help would be greatly apprecitated. Thanks, Mark ...

Sections breaks and printing
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi, <br><br>I am trying to print a document that contains multiple section breaks, as some pages are landscape and others are portrait. When it comes to print, it is printed as multiple documents. This happens with PDF export both from the print dialog and the save as command. <br><br>I could print to PDF and then combine each file using acrobat pro, but this is a lot of work. Is there any way to either have different orientations (with different header and footers) without using page br...

IF Statement equalling multiple words. Please Help
I want to say if cell A1 = "word1" or "word2" or "word3", then "". Otherwise do this. I know how to make check if it = 1 item, but how do you make it check 3 or four things without using multiple IF statements? Thanks Phil Use and If(or(A1="word1",A1="word2",A1="word3"),then"",else) That should work for you, Cody "Phillip Vong" wrote: > I want to say if cell A1 = "word1" or "word2" or "word3", then "". > Otherwise do this. > > I know how t...

Beginner needs help
hello together, i spent several hours on solving the following problem: all i want is a = application with a single document horizontally divided by a splitter. = within the above view(?) i want to be able to scribble (like in the = appropriate tutorial of microsoft) while the bottom view(???) should be = able to receive and display text. additionally i want to save and print = everything together (common document?). even though i read many = tutorials about splitters, i am far from solving this task. is there = anybody who can tell me something about the basic structure i have to = use - or ...

Active X Checkbox moves after printing
I open an existing document with check boxes on the right half of the page and after printing the checkboxes move the the left column. I created a new excel spreadsheet and added check boxes and the same thing happened after printing. This is only happening on Office XP w/ SP3. I have tried on several machines with SP3 and it works fine. I upgraded one of the working machines to SP3 and the problem started happening. Has anyone had this problem? xl2002??? http://support.microsoft.com/default.aspx?scid=kb;EN-US;838910 Controls move to the left of the worksheet in Microsoft Excel 20...

Where are macros stored?
Specifically, where are Macros stored? Is it in my C drive?I've created some Macros, chosen them to be stored in Personal macro workbook instead of the other choices, and they are now arbitrarily stored in Personal.xls. Is Macros are stored in workbooks. The next question is "where are workbooks stored?" If you record a macro, you're given the option of what workbook to record into. If you choose personal.xls, they'll go there. If you choose, thisworkbook or new workbook, they'll go there. (I always record a macro into a test workbook. Then I can tweak ...

Learning CRM
Hi, I really need your advice. I am very experienced in .NET, C#, Winforms, SQL, IIS, ASP.NET as well as VSTO and MS Office. Now, I am interested in learning the ins and outs of Microsoft's CRM product from a developer / integration perspective and need to chart an "education" path. Please correct me if I am missing anything but the areas I would need to know are: - Setup, Installation and Deployment; - Detailed CRM feature usage and configuration; - CRM native customization capabilities; - CRM customization extensions (.NET, add-ons, plug-ins, etc....) Though I am p...

Selecting rows from various sheets #3
That's pretty much what I want, but I want the function to select al the relevant columns and not just 1 column. Shan -- sha ----------------------------------------------------------------------- shav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1198 View this thread: http://www.excelforum.com/showthread.php?threadid=26665 Hi then just add some more VLOOKUPs. One for each column of your source sheet -- Regards Frank Kabel Frankfurt, Germany "shav" <shav.1dodta@excelforum-nospam.com> schrieb im Newsbeitrag news:shav.1dodta@excelforum-...

EXCEL FORUM
My last 2 posts wouldn't let me post a portion of a sheet with row an column titles. It would make asking the wuestion so much easier t show it? I've seen it done. Help please! Healingbea -- Message posted from http://www.ExcelForum.com If you can explain your problem in plain text, you'll get more reviewers. Many people won't open workbooks (too many threats of malicious code). And many people who use a newsreader and access the newsgroups directly (not via excelforum) won't even see your attachment. "Healingbear <" wrote: > > My last 2 post...