VBA FreezePanes on multiple worksheets in my workbook

[rezacs]

I created a vba macro that helps us apply some formatting to our reports before we send them out.

At this point everything works on a workbook with a single worksheet.

However, if there are multiple worksheets I am unable to apply FreezePane to more than the first worksheet.

I tried to change ActiveWindow to ws but it fails, I also tried adding a FreezePane = False before the row selection and true.

Can anyone help me, i'm stuck.

Here is my code:

Sub FormatForPrint()


Dim ws As Worksheet


Dim dateString As String, TheDate As Date
Dim valid As Boolean: valid = True


Do
  dateString = Application.InputBox("Enter Data as of Date (m/d/yyyy): ")


  If IsDate(dateString) Then
    TheDate = DateValue(dateString)
    valid = True
  Else
    MsgBox "Invalid date"
    valid = False
  End If
Loop Until valid = True


Dim row As Integer, PRow As Integer
    PRow = Application.InputBox("Enter row number to filter: ")


For Each ws In ActiveWorkbook.Worksheets
    With ws
        If .Name <> "Report Criteria" Then
            ws.PageSetup.LeftMargin = Application.InchesToPoints(0.25)
            ws.PageSetup.RightMargin = Application.InchesToPoints(0.25)
            ws.PageSetup.TopMargin = Application.InchesToPoints(0.75)
            ws.PageSetup.BottomMargin = Application.InchesToPoints(0.75)
            ws.PageSetup.HeaderMargin = Application.InchesToPoints(0.3)
            ws.PageSetup.FooterMargin = Application.InchesToPoints(0.3)
            ws.PageSetup.Zoom = False
            ws.PageSetup.FitToPagesWide = 1
            ws.PageSetup.FitToPagesTall = False
            Rows(PRow + 1 & ":" & PRow + 1).Select
            ActiveWindow.FreezePanes = True
            ws.PageSetup.LeftHeader = "&""Arial Narrow""&9 &F"
            ws.PageSetup.RightHeader = "&""Arial Narrow""&9Data as of " & TheDate
            ws.PageSetup.LeftFooter = "&""Arial Narrow""&9 &Z&F"
            ws.PageSetup.RightFooter = "&""Arial Narrow""&9Page &P of &N" & Chr(10) & "Printed &D"
            If ws.AutoFilterMode = False Then
            ws.Rows(PRow).AutoFilter
            End If
        End If
    End With
    Next ws
End Sub

 

[rezacs]

And of course I just found the answer just minutes after I asked.

Added
Application.Goto Reference:=.Range("A1"), Scroll:=True

on the line before
Rows(PRow + 1 & ":" & PRow + 1).Select

and it worked.

 

[steve the fish]

Im not sure it will cancel the freeze pane should PRow change though.

 

[steve the fish]

Heres a way:

For Each ws In ActiveWorkbook.Worksheets
    With ws
        If .Name <> "Report Criteria" Then
            .Activate
            With ActiveWindow
                .SplitRow = PRow + 1
                .SplitColumn = 0
                .FreezePanes = True
            End With
        End If
    End With
Next ws

 

[rezacs]

Thanks, that worked nicely as well. Did need to remove the +1 after PRow in this case, otherwise it was freezing the row under the header row.

Do you know, would there be a benefit to using this over the other option I had found? Less prone to error perhaps?

I figured we would have the issue with the autofilter and freezepanes if the row changed on other sheets, most of our reports are fairly simple so it isn't too concerning, however we do have a few reports where this would happen and we would just need to correct that as needed. Do you know if there is a way to address that? Maybe something that can determine the header row automatically?