alu
06-14-2007, 07:58 AM
Hi, Lucas gave me this great bit of Code (EDIT: It Makes all sheets fit to 1 page wide and infinite tall & sets up the margins):
Option Explicit
Sub SetupAllSheets()
'
Dim M As Long, N As Long, Firstsht As Long, Lastsht As Long, Sheet As Object
'
Lastsht = Sheets.Count
M = 0: N = Lastsht
'
For Each Sheet In Sheets
If Not Sheet.Visible Then N = N - 1
If Sheet.Visible And Sheet.Type = xlWorksheet Then
If WorksheetFunction.CountA(Sheet.UsedRange) = 0 Then
N = N - 1
End If
End If
Next
'
For Firstsht = 1 To Lastsht
'
If Sheets(Firstsht).Visible = True Then
'
If Not TypeName(Sheets(Firstsht)) = "Chart" Then
If WorksheetFunction.CountA(Sheets(Firstsht).UsedRange) <> 0 Then
M = M + 1
GoSub DoPrint
End If
Else 'else it's a chart
M = M + 1
GoSub DoPrint
End If
'
End If
'
Next 'Firstsht
Exit Sub
DoPrint:
With Sheets(Firstsht).PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.196850393700787)
.RightMargin = Application.InchesToPoints(0.196850393700787)
.TopMargin = Application.InchesToPoints(0.78740157480315)
.BottomMargin = Application.InchesToPoints(0.78740157480315)
.HeaderMargin = Application.InchesToPoints(0.196850393700787)
.FooterMargin = Application.InchesToPoints(0.196850393700787)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With
With Application
.EnableEvents = False
' Sheets(Firstsht).PrintPreview '.PrintOut
.EnableEvents = True
End With
Return
End Sub
The thing is my data only uses column A-N and column O is still there, if I delete or hide column O, column P appears etc... any ideas? thanks all...
Option Explicit
Sub SetupAllSheets()
'
Dim M As Long, N As Long, Firstsht As Long, Lastsht As Long, Sheet As Object
'
Lastsht = Sheets.Count
M = 0: N = Lastsht
'
For Each Sheet In Sheets
If Not Sheet.Visible Then N = N - 1
If Sheet.Visible And Sheet.Type = xlWorksheet Then
If WorksheetFunction.CountA(Sheet.UsedRange) = 0 Then
N = N - 1
End If
End If
Next
'
For Firstsht = 1 To Lastsht
'
If Sheets(Firstsht).Visible = True Then
'
If Not TypeName(Sheets(Firstsht)) = "Chart" Then
If WorksheetFunction.CountA(Sheets(Firstsht).UsedRange) <> 0 Then
M = M + 1
GoSub DoPrint
End If
Else 'else it's a chart
M = M + 1
GoSub DoPrint
End If
'
End If
'
Next 'Firstsht
Exit Sub
DoPrint:
With Sheets(Firstsht).PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.196850393700787)
.RightMargin = Application.InchesToPoints(0.196850393700787)
.TopMargin = Application.InchesToPoints(0.78740157480315)
.BottomMargin = Application.InchesToPoints(0.78740157480315)
.HeaderMargin = Application.InchesToPoints(0.196850393700787)
.FooterMargin = Application.InchesToPoints(0.196850393700787)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With
With Application
.EnableEvents = False
' Sheets(Firstsht).PrintPreview '.PrintOut
.EnableEvents = True
End With
Return
End Sub
The thing is my data only uses column A-N and column O is still there, if I delete or hide column O, column P appears etc... any ideas? thanks all...