Print Issue and Saving Issue

H

hce

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
 
D

Dave Peterson

#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...)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top