Print Issue and Saving Issue

Dear All

Firstly, is it possible to use VB code to create a macro that allows m
to save multiple worksheets in a workbook as csv files and the name o
the csv files to be defined by the tab names... for example UK(Sheet1
would be saved as UK.csv... and so on...

Secondly, is it possible to create a macro that prints define
worksheets... I only want to print 5 pages of data if there's more tha
5 pages of data and if there's less than 5 pages of data, then prin
all... hence all print outs should not be more than 5 pages... Also, a
my vb knowledge is limited, i only know how to define my macro to prin
by using this command... Sheet1.Select... i currently has a macro t
help me print but it will print 5 pages of all defined worksheets whic
is a waste of paper because there's some worksheets that has no data..
or not much data... this is my command... Sheet1.Select then cal
printmacro... 

I would really appreciate if anyone could suggest a better way for m
to do all these stuff...

cheer

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

0
5/4/2004 11:55:45 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
447 Views

Similar Articles

[PageSpeed] 22

#1:  

Option Explicit
Sub testme()

    Dim wks As Worksheet
    Dim newWks As Worksheet
    
    For Each wks In ActiveWorkbook.Worksheets
        wks.Copy 'copies to a new workbook
        With ActiveSheet
            .Parent.SaveAs Filename:="C:\WINDOWS\TEMP\" & .Name, _
                FileFormat:=xlCSV
            .Parent.Close savechanges:=False
        End With
    Next wks
    
End Sub

Copy the worksheet to a new workbook and save from there.

#2.  I did a file|print and said to print pages 1 to 5 of a worksheet that only
was using one page.  Excel didn't try to print more than 5 pages and was happy
to print only one if that's all I had on the worksheet.

Option Explicit
Sub testme02()
    Dim wks As Worksheet        
    For Each wks In ActiveWorkbook.Worksheets
        wks.PrintOut From:=1, To:=5, Preview:=True
    Next wks
End Sub

And if you had some unused worksheets, you could check this way:

Option Explicit
Sub testme02B()
    Dim wks As Worksheet
    For Each wks In ActiveWorkbook.Worksheets
        If wks.UsedRange.Address(0, 0) = "A1" _
         And IsEmpty(wks.Range("a1")) Then
            'do nothing
        Else
            wks.PrintOut From:=1, To:=5, Preview:=True
        End If
    Next wks
End Sub

====
Drop the ", Preview:=True" when you're ready to try it with paper.  (I wanted to
save some trees...)





"hce <" wrote:
> 
> Dear All
> 
> Firstly, is it possible to use VB code to create a macro that allows me
> to save multiple worksheets in a workbook as csv files and the name of
> the csv files to be defined by the tab names... for example UK(Sheet1)
> would be saved as UK.csv... and so on...
> 
> Secondly, is it possible to create a macro that prints defined
> worksheets... I only want to print 5 pages of data if there's more than
> 5 pages of data and if there's less than 5 pages of data, then print
> all... hence all print outs should not be more than 5 pages... Also, as
> my vb knowledge is limited, i only know how to define my macro to print
> by using this command... Sheet1.Select... i currently has a macro to
> help me print but it will print 5 pages of all defined worksheets which
> is a waste of paper because there's some worksheets that has no data...
> or not much data... this is my command... Sheet1.Select then call
> printmacro...
> 
> I would really appreciate if anyone could suggest a better way for me
> to do all these stuff...
> 
> cheers
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/5/2004 1:57:31 AM
Reply:

Similar Artilces:

Printing Problems
When printing calendar event or a note Outlook fail with an error. The printers work fine with emails. Does any one have a suggestion on how to correct this problem? Thanks, Steve ...

reports that print after the stock count
Hello: To the best of my knowledge, the only report that automatically prints at the end of a stock count is the exceptions report. A client claimed that, during last month's stock count, a "quantity report" of some sort automatically printed. They conducted a stock count this month and were surprised not to see this "quantity report" automatically print. I don't think that there is a such thing as a "quantity report" printed after a stock count. Is there? I advised the client to print the Historical Stock Status Report, since this other report...

Daylight Savings Time Changes
Hello, Does anyone know if the patch for Exchnage 2003 identified in KB926666 has to also be applied to every server that is running the Exchange Tools (System Manager)? Thanks in advance K On Wed, 31 Jan 2007 10:24:03 -0800, Kerry <Kerry@discussions.microsoft.com> wrote: >Hello, > >Does anyone know if the patch for Exchnage 2003 identified in KB926666 has >to also be applied to every server that is running the Exchange Tools (System >Manager)? > >Thanks in advance >K Im all for consistency. If the patch isnt needed, it shouldnt install, so I would at ...

saving excel document as pdf
Is it possible to save an excel sheet in pdf format while keeping drop down menus workable on the pdf file? I mean suppose you have a drop down menu in an excel sheet where you can choose numbers. Is it possible to save this file as a pdf file while allowing the drop down menus still work in the pdf file? Or am I asking too much? Hi No, it can't be done, AFAIK -- Regards Roger Govier "minimus" <minimus@live.co.uk> wrote in message news:h0arb5$glm$1@news.eternal-september.org... > Is it possible to save an excel sheet in pdf format while keeping drop > ...

saving as xml
Hi, The following url give you an XML output http://webservices.titantvguide.titantv.com/contentblocks.asmx/TonightOn?registrationKey=g8byqgPai3PYi1i7VOfEcKDfKOeYwmYM is there a way to save this as an xml file on to the server? Thanks in Advance. Regards Manoj Manoj Paramu Das wrote: > is there a way to save this as an xml file on to the server? What's the difference between saving XML file and HTML file? Just create WebRequest, get response stream and save it. -- Oleg Tkachenko XML Insider http://www.tkachenko.com/blog ...

opened Excel w/sheet from email attachment, updated, saved & cant
I emailed an Excel worksheet from my office to my home - opened it from my Outlook Express folder - updated file extensively - saved several times during the four hours I worked - closed & now can't find the updated file. I forgot I had opened it from the email and never saved it to my C drive. However, I saved it several times while I was working on it. Shouldn't the file in my email be the updated saved file? It's not and I can't find it anywhere. I believe this happened once before to me. Any suggestion would be appreciated. I've lost a lot of work if the file...

Im desperate to fix my fragmented memor...will advanced server save me with the 3 gb switch?
Im desperate to fix my fragmented memory, will advanced server save me with the 3 gb switch? The Situation: -Server=Windows 2000 Server sp4. -2.5 gb of RAM -Exchange=Enterprise Ed with all service packs and hotfixes. -Group Shield virus scanning Every month I recieve eventid 9582's errors, and then 12800 mail processing errors. THIS IS FREAKING KILLING ME AT WORK. Here is what I have tried. -Regedit ajustment to the heep. -ADSI ajustment to the IS (which oddly removes the errors from the event log, however MOM still detects low virtual memory errors). -sp3 (which claims to have fixed ...

Purchase Order Printing
I'm trying to print a modified POP Purchase Order Blank Form to a printer and all that comes out are blank sheets. If I print to screen, I see the report as it should be. As soon as I send it to the printer, I just get blank sheets. The report works correctly on other computers, and I tried re-importing the report on the messed up computer, but it did not resolve the issue. If I just print a blank POP Purchase Order Blank Form to a printer, it prints fine...with X's in every available space in the form. I'm out of ideas, let me know what I can try. Thanks. ...

XML Serialization Issue
I recently added a new property to a base object that had a property with a protected set. After adding the new propery to the class that inherited from that class, I get the below error. This web service was working until this change. System.InvalidOperationException: Unable to generate a temporary class (result=1). error CS0272: The property or indexer 'USI.Data.Common.BizBase.IsValid' cannot be used in this context because the set accessor is inaccessible error CS0272: The property or indexer 'USI.Data.Common.BizBase.IsValid' cannot be used in this context because the...

close workbook without saving or promt to save
i am new to excel 2007 and when closing the only visible sheet in my workbook i need it to close the workbook without offering option to save. i have tried to use macros but i dont think im doing it correctly. i have a active button that works but i would like it to work when you close from the top right x or exit excel button. shane In Thisworkbook module of your workbook. Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.Saved = True End Sub Note you have to save at least once before closing first time or you will lose the code your just pasted into the...

print folder in table style
Hello all, My CU wants to print a list of files in a folder. He use Outlook 2002. Click Other Shortcuts -> My computer -> choose a folder -> Click the print icon on toolbar -> choose Table Style -> print preview The header and footer can be printed properly. However the content is blank. I mean: there is no file in the list. I can print properly at my side. However at the CU side, there are 2000 users encounter the same problem. And there are four kinds of printer. All have this problem. Any suggestion? Thanks! ...

Saving file for new year
I would like to redo my Money file for the new year. I have transactions back to 2003. I'd like to create a new file that begins with 2008. I remember years ago that Quicken gave me that option. Is there anyway to do this with Micorsoft Money? In microsoft.public.money, grsode wrote: >I would like to redo my Money file for the new year. I have transactions >back to 2003. I'd like to create a new file that begins with 2008. I >remember years ago that Quicken gave me that option. Is there anyway to do >this with Micorsoft Money? File->New->NewFile lets ...

saving and remaming files
Folks, Have a small company with 5 pc's networked with Server 2000. Recently users have complained that when they open excel files (that are stored on the server and shared by all users) and make changes...they hit "save" (as they always have) and it renames the file as (if the secretary has recalled the error message correctly) as a temp file. This has obviously caused problems with the other users. When they go to open the folder (after changes have been made by another user) they do not see the changes. They have combated the problem even further as they have started to rena...

Slow print preveiw Excel 2003 SP3
Intermittenly when I click on print preview, it is as if Excel is hanging, it takes a long time before the print preview appears on the screen. Also when I click on "setup" on the print preview screen the same thing happens. Any help will be appreciated. If you change to a different printer, do you have the same problem? If no, then I'd visit the printer's web site and look for newer versions of the printer driver. Gabriel van Rensburg wrote: > > Intermittenly when I click on print preview, it is as if Excel is hanging, it > takes a long time before the print ...

Outlook plugin for saving mails by dragging
Hi all, Our Legal team want some way of saving all emails related to a project into a common folder. We use microsoft exchange but our exchange admins will not allow use of public folders. The only way i can see around it is for the staff to setup a folders in the shared secure file area and save the emails they want into these. However, while the idea sounds perfect to them, they aren't happy with idea of saving individual emails to the folder one at a time, instead they would prefer some way of dragging them to the folder. I was wondering if anyone knew of an Outlook plugin or any other...

save font with file
I have a font for GDT symbols installed on my PC, there is no problem to display and print this document from my PC, but when I try on another PC which do not have installed the GDT font, this is missed. how can I save the font with the excel file? Hi, I'm not an expert in this area, but I believe you need to have the fonts installed on each pc that needs it; a good reason to keep to familiar fonts, I guess. jeff >-----Original Message----- >I have a font for GDT symbols installed on my PC, there >is no problem to display and print this document from my >PC, but whe...

Cell identity when printing comments on separate page
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When printing comments at end of page, it identifies the cell by letters and numbers. (i.e. Cell: AA33) Afterward the comment is shown for that cell. <br><br>Question: Instead of the cell identity being shown this way, is it possible ithe cell can also be identified by its X axis header for that column with the y access header for that Row. <br><br>Thank for your assistance. <br><br>Carl Sorry Carl, but I'm not clear on what you're asking for... In 'MySpeak' ...

Printing Font color
Can anyone tell me how to print normal text in a color other than black? Have tried all types of fill for text (both black and gray) and still always prints BLACK. Ellen this would be an issue of your printer driver - not Publisher. The issue you describe is unknown in Publisher. However, we can get to the bottom of it for you. What version of Windows and Publisher are you using? Is this 'normal text' in a Text Frame or WordArt? What brand and model of printer are you using? -- ...

How do I stop my Publisher 2003 document from saving as a webpag.
When I save a particular publication ( a newsletter) in Publisher 2003 as a Publisher 2000 document (so I can work on it elsewhere) it opens as a web page instead of a print document. When I convert it back to print, I lose a few inches of print on the left page that just don't print even when I change the margins. The missing print shows on the screen normally, it is only in print preview and on paper that it cuts off. Can I stop this from happening? Can I retrieve the missing print? Please help! You may have to reconstruct this publication. -- Mary Sauer MSFT MVP http://off...

2 panel brochure printing in publisher
I am trying to print a 2 panel brochure in publisher and when I print preview everything fits but when it prints pages 1, & 3 get cut off. I would like all the pages to fit and print so the pages are aligned when folded. its an 8x11 normal sheet. I don't know which margins to use to make it print correctly. Please help! Have you setup your brochure as a booklet? Do you know your printers margin limitations? A tutorial is here: http://ed.mvps.org/Static.aspx?=Publisher/horidiag -- Mary Sauer MVP http://msauer.mvps.org/ "Dominikin" <Dominikin@d...

Print Preview
I have posted an excel spreadsheet that colleagues access through Internet Explorer. When the spreadsheet is accessed the print preview command is no longer on the menu, BUT print preview is available in Excel and Internet Explorer normally. I have right clicked the toolbar and the option is just not available. Help!! Stewart Walker MOS Excel Expert (not that good though!) Do you mean that you right clicked the toolbar, chose Customize and then under command tab chose categories, file and the icon was missing from the command list? --- Message posted from http://www.ExcelForum.co...

Print a letter from a workflow
Hi, Is it possible to run a workflow that would print a letter. In other words when an accout is created (with certain conditions), letter a is sent t the printer. then wait 2 months then letter b is sent then wait 2 months then letter c is sent. All of these letters would need to be a template (ie with name address tags) Thanks in advance On Dec 8, 7:01=A0pm, Tango <Ta...@discussions.microsoft.com> wrote: > Hi, > > Is it possible to run a workflow that would print a letter. > > In other words when an accout is created (with certain conditions), lette= r a > is ...

Print part of contact list
I have a list of contacts. This contains their names and addresses, phone numbers, etc. I want to print out just their names in one long list. I can't see how to exclude the addresses, etc from this list. Can anyone help here? Thanks Rob Graham Use a mail merge. -- Russ Valentine [MVP-Outlook] "Rob graham" <rttgrahamwow@btinternet.com> wrote in message news:X_WdnbHs0L8HlrLbnZ2dnUVZ8vydnZ2d@bt.com... >I have a list of contacts. This contains their names and addresses, phone >numbers, etc. I want to print out just their names in one long list. I >can'...

Gantt Chart Missing One Task Line When Printed
Hello! I am completely stumped here with Project 2007. When printing the gantt chart line 30 of my plan is blank. The line number is there, but the task info and bar is blank. It shows up in print preview. All other lines show up fine and the rest of the project prints fine as per my print settings - just missing one line! Printer drivers are updated, software has current update - anyone ever heard of this? I really appreciate your help! Hi Jean, Do you see a different answer if you turn off 3D bars? Tools > Options, View tab. There were some early problems with 3D ...

Using VBA to Print PDF Files
Hi: My customer has PDF spec sheets for their inventory items. When a pick ticket is printed, they wish to print the PDF spec sheets for each item on the pick tickets. I am very comfortable with the report event structure and know exactly how to get to the PDF files using VBA, but I am having a problem getting the docs printed. I've taken two approaches: 1. Drop ACROPDF.dll onto a User Form in the VBA project. To do this, I created a procedure on the form as shown: Public Sub PrintPDF(FileName As String) AcroPDF1.LoadFile (FileName) AcroPDF1.printAll PDFPrinter.Hide End Sub T...