Trouble with Range objects in For Each...Next loops

[godawgs85]

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

[CatDaddy]

Sub SetPrintArea()
'Declarations
Dim lastCell As Range
Dim i As Integer
'Code

for i = 1 to ActiveWorkbook.Sheets.Count

With ActiveWorkbook.Sheets(i)
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell)
sht.PageSetup.PrintArea = Range(Cells(1, 2), lastCell).Address
Set lastCell = Nothing
End With

next i

[Chabu]

I suspect the only thing you need to change to your code is prefix the "Cell" methods with "sht." so that they refer to the sheet in the loop rather than to the active sheet.

Sub SetPrintArea()
'Declarations
Dim lastCell As Range
Dim sht As Worksheet
'Code
For Each sht In ActiveWorkbook.Sheets
Set lastCell = sht.Cells.SpecialCells(xlCellTypeLastCell)
sht.PageSetup.PrintArea = Range(sht.Cells(1, 2), lastCell).Address
Set lastCell = Nothing
Next sht

End Sub

[godawgs85]

Thanks! I input the sht prefixes and it worked like a charm. :)