Formatting All Sheets in a Workbook so I can Print

[Guest]

I download a lot of excel workbooks from data providers. Unfortunately none
of the worksheets in these workbooks are formatted correctly.

On a worksheet I have to go into Page Setupt to 1) adjust the page
size,usually to 75%; 2) adjust left and right margins to .2 and top and
bottom margins to .5, 3) Change rows to repeat at top; 4) Change columns to
repeat at left, and 5) set a print area -- a range around all data. Then I
have to repeat! It drives me crazy.

I was thinking about a solution like this:
A master worksheet with a "control panel, with the following controls:

Apply the following format to:

Col A Col B Col C
Col D to xxx
Select Workbook Name Worksheet Name formats

Format types in Col D, E, F, etc... Margin Left, Margin Right, Margin Top,
Margin Bottom, Adjust to % Size, Set Print Area to Range of all Value/Text
(Y/N), and I guess that's it.

this "control panel" will help be build a library of formatting controls for
any ugly formatted workbook I download. In addition to building a library of
names to apply formatting to, it would also be good to have the option to
apply formats for all active work books, all active worksheets.

My question is: Does somethign like this already exist, if so, where can I
find it? If not, does anybody have a solution? It doesn't seem like it
should be a big deal, but since I have seen any solution to this I guess it
is...

Thanks very much...

SteveC

 

[Don Guillett]

Something like this in your personal.xls with a custom button assigned to
it.

Sub Fix_Page()
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
Application.ScreenUpdating = True
ActiveWindow.SelectedSheets.PrintPreview
End Sub

 

[Guest]

Don, thanks

I see I have to run the macro in the active sheet.

Assuming I build a workbook called Control Panel.xls, and I save buttons
assigned to macros in a worksheet "Controls" of ControlPanel.xls, what lines
of code do I have to add to assign the macro to run on a specific worksheet
name in a newly downloaded workbook?

When I download data from a data provider, the workbook names are usally the
same, but the data and worksheet names are almost always different...

Is there a line of code to change the size of the print-out page? (In Page
Setup / Page it's: Scaling--adjusts to x% normal size in Page Setup / Page).


Is there a line of code to change from portrait to landscape?

I see there is a line .Zoom = 100 -->I changed that to 50, but no
effect on view zoom or print scale...

Wondering also how I can set print area for all text/values in the
worksheet... but I bet the answers to the 4 above questions are good
enough... this last one might be tough

thanks a lot for your time... I really appreciate it... SteveC

 

[Don Guillett]

Try recording a macro on your own and then modifying to suit your needs. If
you need more help, come on back.

 

[Guest]

You could also right click on a workbook tab, click select all worksheets,
then do your formatting and it will be applied to all worksheets.

The downside is even those formatting options you do not change will be
applied (such as headers and footers).

 

[Guest]

I combined your macro with another one from a Microsoft MVP... I forget his
name now, but will reference him when I remember. The MVP macro cycles the
macro for all worksheets in the book (see below).

I created the macro, and saved it in a workbook called "Format.xls"

Then I assigned a button to the macro (view, forms, click the button you
want to use, click the macro you want to assign).

Then, whenever I come across an ugly workbook that I want to format, I copy
the button from "Format.xls" paste it into the ugly workbook and run it. It
then formats all worksheets the same way.

Over time when I come across variations of ugly workbooks, I will modify and
rename the macro, and assign it to a new button. That way I will have a
catalog of formatting buttons I can apply to all kinds of ugly workbooks.

When I say ugly, I mean formatted in a way that is not conducive to
printing.

It's good enough for what I need right now. Maybe there is a more elegant
solution down the road... Thanks for your help.

Sub ScaleCodeForEachSheet()
Dim WS_Count As Integer
Dim I As Integer

WS_Count = ActiveWorkbook.Worksheets.Count
Application.ScreenUpdating = False
For I = 1 To WS_Count
Worksheets(I).Activate

Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.2)
.RightMargin = Application.InchesToPoints(0.2)
.TopMargin = Application.InchesToPoints(0.2)
.BottomMargin = Application.InchesToPoints(0.2)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 75
End With

Next I
Worksheets(1).Activate
Application.ScreenUpdating = True
End Sub