Page set up for all worksheets

[Jodie]

Hello,

I am trying to run the following macro for all sheets in a workbook:

Sub VBAMacro()
Cells.Select
Range("D1").Activate
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.PrintGridlines = True
.Orientation = xlLandscape
.PrintTitleRows = ""
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 5
End With
ActiveWorkbook.Save
End Sub

How can I make it work?

 

[Patrick Molloy]

sub Main()
dim ws as worksheet
for each ws in worksheets
ws.activate
VBAMacro
next
End Sub

 

[john]

not tested but something like following should do what you want.

Sub VBAMacro()
Dim ws As Worksheet

Application.DisplayAlerts = False

With ActiveWorkbook

For Each ws In .Worksheets

With ws.PageSetup

.PrintArea = ""
.PrintGridlines = True
.Orientation = xlLandscape
.PrintTitleRows = ""
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 5

End With

Next ws

.Save

End With

Application.DisplayAlerts = True

End Sub

 

[Jodie]

This works great. Thank you both. Now, I need to hide columns in these same
worksheets. Are either of you up for that. Idealy, I would like to hide any
columns that are blank after row 1. If that is not possible, I could select
a specific column that I know is blank, column P.

 

[john]

Hi jodie,

see if this does what you want (assumes using 2003)

Sub HideEmptyCols()

Dim iCol As Integer
Dim ws As Worksheet

'Excel 2003

Application.ScreenUpdating = False

For Each ws In Worksheets


For iCol = 256 To ws.Range("IV1").End(xlToLeft).Offset(0,
1).Column Step -1

If IsEmpty(ws.Cells(65536, iCol)) And _
IsEmpty(ws.Cells(1, iCol)) Then

If iCol > 1 Then

ws.Cells(iCol, iCol).EntireColumn.Hidden = True

Else

ws.Cells(iCol, iCol).EntireColumn.Hidden = False

End If

End If

Next iCol

Next ws

Application.ScreenUpdating = True

End Sub

 

[Jodie]

John, it worked except it still left the columns with a header in row 1 even
though there is nothing else in the column. Also, I realized that what I
should have asked for was to hide the column if all of the cells are blank or
have a value of 0 (zero). Is this possible? I appreciate your help.

 

[john]

Hi jodie,
sorry for slow reply, went out for evening.

see if this mod helps:

Sub HideEmptyCols()

Dim iCol As Integer
Dim ws As Worksheet
Dim rRange As Range

'Excel 2003

Application.ScreenUpdating = False


For Each ws In Worksheets

For iCol = 256 To 2 Step -1

If Application.WorksheetFunction.Sum(ws.Range(ws.Cells(2, iCol), _

ws.Cells(65536, iCol))) = 0 Then

ws.Cells(, iCol).EntireColumn.Hidden = True

End If

Next iCol

Next ws

Application.ScreenUpdating = True

End Sub

 

[Jodie]

Hi John, I hope you had a fun weekend.

I tried this out and it is hiding my columns that are blank and the colums
that have an alpha value. The columns with zero are still present. Is there
something that I can change that will keep the alpha columns and hide the
zero columns?