Print area and settings via VBA

[EconSean]

Greetings everyone,

I have four files, each with 66 worksheets in it (so a total of 264 tables).

Within the sheets I have 11 versions of Table 1, and 11 versions each of Tables 2 through 6. The worksheet tab names all start with Table 1, Table 2, ... Table 6, with the remaining name describing a bit more what are in the tables (e.g., total, male, female).

The print area for all of the Table 1s is the same, as is the page setup. Similarly, Tables 2 through 6 are all identical (but different from Table 1).

I would like to use code to set the print areas and page setup specs. I've tried the code below, but it doesn't work. It runs for quite a while, but I don't see that it's setting any print areas or page setup specs. Now, if I click a tab, say one of the Table 1s, then run just the macro for the Table 1 setup, it does seem to work. Given that I have so many tables, I'd rather not have to do this manually (although this is still faster than having to set the print area and page setup specs by hand on every table).

Anyway, my code is intended to check the worksheet tab name, and if it's one of the Table 1s, do one print area/page setup routine, and if it's not one of the Table 1s, do the other routine.

Any assistance that you might provide would be greatly appreciated.

Sub Setup()

Dim sht As Worksheet, TableName As String

TableName = "Table 1"

For Each sht In ThisWorkbook.Sheets

If Left(sht.Name, 7) = TableName Then
Call Table1
ElseIf Left(sht.Name, 7) <> TableName Then
Call Tables2thru6
End If

Next sht

End Sub

Sub Table1()
Range("A1:K67").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$K$67"
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 = 600
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
'ActiveWindow.SelectedSheets.PrintPreview
Range("A2").Select
End Sub

Sub Tables2thru6()
Range("A1:K25").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$K$25"
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 = 600
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
Range("A2").Select
End Sub

 

[pbornemeier]

You may want to consider using the Page Setup Excel4 Macro to establish the print settings for each worksheet. It is much faster, especially if you are printing to a network printer. This thread discusses it:

http://www.mrexcel.com/forum/showthread.php?t=362900

Be sure to look at the last post.

This thread has a link to the XL4 Macro Help File, a link to some dialogs that may be of help and some good discussion:

http://www.mrexcel.com/forum/showthread.php?t=340844

 

[EconSean]

Thank you Phil, that was a big help (and educational to boot).