godawgs85
06-17-2011, 01:58 PM
Hi all,
I'm having some trouble with range objects in any For Each...Next loops i'm using to work through sheets. The code below is written to loop through each sheet in the workbook and set the print area to the desired range, which in this case is dynamic. The code works well except that when I execute it it only sets the print area on whatever sheet is active when I execute the code. The rest of the sheets remain as they were. The code is only working on the active sheet and not properly looping through all. Can anyone help me fix? Thanks in advance!
Sub SetPrintArea()
'Declarations
Dim lastCell As Range
Dim sht As Worksheet
'Code
For Each sht In ActiveWorkbook.Sheets
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell)
sht.PageSetup.PrintArea = Range(Cells(1, 2), lastCell).Address
Set lastCell = Nothing
Next sht
End Sub
I'm having some trouble with range objects in any For Each...Next loops i'm using to work through sheets. The code below is written to loop through each sheet in the workbook and set the print area to the desired range, which in this case is dynamic. The code works well except that when I execute it it only sets the print area on whatever sheet is active when I execute the code. The rest of the sheets remain as they were. The code is only working on the active sheet and not properly looping through all. Can anyone help me fix? Thanks in advance!
Sub SetPrintArea()
'Declarations
Dim lastCell As Range
Dim sht As Worksheet
'Code
For Each sht In ActiveWorkbook.Sheets
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell)
sht.PageSetup.PrintArea = Range(Cells(1, 2), lastCell).Address
Set lastCell = Nothing
Next sht
End Sub