S
SatYricoN
Hello, Can anyone help me to write a little macro to be used to print
large quantities of Excel files?
I print hundreds of Excels every day and I thought the macro will be
very helpful to make my work easier.
The macro consists of 7 easy steps below.
1. All sheets select (sheet1 active)
2. Press on the little square, above cell 1 and on the left side of row
A (all sheets become black)
3. Page break preview
4. Column Autofit selection
5. Page setup: (Page tab - orientation: Landscape; Adjust to: 65%;
Paper size: Letter; print quality: 300 dpi; Margins tab: Horizontally
and Vertically; Sheet tab: Over, then down)
6. Zoom: 25%
7. Print Preview
Please note the following: all operations to be done with sheet1 active
with all sheets selected.
I noticed that if some sheet at the middle is active and even if the
whole work book is selected not every sheet gets the same task,
but only those that follow the selected sheet. Also sheet1, sheet2, and
sheet3 are named by default.
Excels that are to be printed mostly have different names, so macro
shouldn't include any name for the sheets, but only "all sheets".
If it's not possible I thought that one extra sheet could be created in
the macro, for instance Sheet "AAA" which will be first and active.
One more thing, excels that I print often have a lot of blank pages and
must be excluded from the printing.
I heard it's virtually impossible to do except by setting print area
and move the blank area out of the printed spots.
So if anyone knows the macro that automatically deselects blank pages
would be just perfect.
To give you a better picture of the whole procedure here is an example.
Any help will be greatly appreciated. my email is (e-mail address removed)
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/18/2003 by Administrator
'
'
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
Cells.Select
ActiveWindow.View = xlPageBreakPreview
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlOverThenDown
.BlackAndWhite = False
.Zoom = 65
End With
ActiveWindow.Zoom = 25
ActiveWindow.SelectedSheets.PrintPreview
End Sub
large quantities of Excel files?
I print hundreds of Excels every day and I thought the macro will be
very helpful to make my work easier.
The macro consists of 7 easy steps below.
1. All sheets select (sheet1 active)
2. Press on the little square, above cell 1 and on the left side of row
A (all sheets become black)
3. Page break preview
4. Column Autofit selection
5. Page setup: (Page tab - orientation: Landscape; Adjust to: 65%;
Paper size: Letter; print quality: 300 dpi; Margins tab: Horizontally
and Vertically; Sheet tab: Over, then down)
6. Zoom: 25%
7. Print Preview
Please note the following: all operations to be done with sheet1 active
with all sheets selected.
I noticed that if some sheet at the middle is active and even if the
whole work book is selected not every sheet gets the same task,
but only those that follow the selected sheet. Also sheet1, sheet2, and
sheet3 are named by default.
Excels that are to be printed mostly have different names, so macro
shouldn't include any name for the sheets, but only "all sheets".
If it's not possible I thought that one extra sheet could be created in
the macro, for instance Sheet "AAA" which will be first and active.
One more thing, excels that I print often have a lot of blank pages and
must be excluded from the printing.
I heard it's virtually impossible to do except by setting print area
and move the blank area out of the printed spots.
So if anyone knows the macro that automatically deselects blank pages
would be just perfect.
To give you a better picture of the whole procedure here is an example.
Any help will be greatly appreciated. my email is (e-mail address removed)
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/18/2003 by Administrator
'
'
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
Cells.Select
ActiveWindow.View = xlPageBreakPreview
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlOverThenDown
.BlackAndWhite = False
.Zoom = 65
End With
ActiveWindow.Zoom = 25
ActiveWindow.SelectedSheets.PrintPreview
End Sub