Print Issue and Saving Issue

Print Issue and Saving Issue

Post by hce » Wed, 05 May 2004 20:55:45

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

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


Print Issue and Saving Issue

Post by Dave Peter » Thu, 06 May 2004 10:57:31


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, _
.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
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...)


Dave Peterson