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

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!

Code:

---

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

---

Code:

---


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

---

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.

Code:

---

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

---

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