Page set up for all worksheets

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?
--
Thank you, Jodie

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

"Jodie" wrote:

> 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?
> --
> Thank you, Jodie

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
--
jb


"Jodie" wrote:

> 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?
> --
> Thank you, 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.
--
Thank you, Jodie


"john" wrote:

> 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
> --
> jb
>
>
> "Jodie" wrote:
>
> > 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?
> > --
> > Thank you, Jodie

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
--
jb


"Jodie" wrote:

> 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.
> --
> Thank you, Jodie
>
>
> "john" wrote:
>
> > 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
> > --
> > jb
> >
> >
> > "Jodie" wrote:
> >
> > > 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?
> > > --
> > > Thank you, 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.
--
Thank you, Jodie


"john" wrote:

> 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
> --
> jb
>
>
> "Jodie" wrote:
>
> > 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.
> > --
> > Thank you, Jodie
> >
> >
> > "john" wrote:
> >
> > > 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
> > > --
> > > jb
> > >
> > >
> > > "Jodie" wrote:
> > >
> > > > 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?
> > > > --
> > > > Thank you, Jodie

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.Cell s(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
--
jb


"Jodie" wrote:

> 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.
> --
> Thank you, Jodie
>
>
> "john" wrote:
>
> > 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
> > --
> > jb
> >
> >
> > "Jodie" wrote:
> >
> > > 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.
> > > --
> > > Thank you, Jodie
> > >
> > >
> > > "john" wrote:
> > >
> > > > 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
> > > > --
> > > > jb
> > > >
> > > >
> > > > "Jodie" wrote:
> > > >
> > > > > 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?
> > > > > --
> > > > > Thank you, 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?
--
Thank you, Jodie


"john" wrote:

> 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.Cell s(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
> --
> jb
>
>
> "Jodie" wrote:
>
> > 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.
> > --
> > Thank you, Jodie
> >
> >
> > "john" wrote:
> >
> > > 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
> > > --
> > > jb
> > >
> > >
> > > "Jodie" wrote:
> > >
> > > > 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.
> > > > --
> > > > Thank you, Jodie
> > > >
> > > >
> > > > "john" wrote:
> > > >
> > > > > 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
> > > > > --
> > > > > jb
> > > > >
> > > > >
> > > > > "Jodie" wrote:
> > > > >
> > > > > > 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?
> > > > > > --
> > > > > > Thank you, Jodie