VBA/Macro: To Amend Freeze Panes

[AxeCM]

Afternoon All,

I currently use the following Code to make changes to formulas across 100's of Excel Reports at once.
Would anybody be able to adjust the section highlighted Red, so I could use it to change the Position of Freeze Panes?

Thank You!


Sub AmendFormula()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String, Fnum As Long
Dim mybook As Workbook
Dim CalcMode As Long
Dim sh As Worksheet
Dim ErrorYes As Boolean

'File Path(UPDATE)
MyPath = "\\local\shared\Team"

'Add a Slash at Path End
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xlsb*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

'Change ScreenUpdating, Calculation and EnableEvents
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

'Loop through all files in the array(myFiles)
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
On Error GoTo 0

If Not mybook Is Nothing Then


'Change Values in These Worksheets(UPDATE)
On Error Resume Next
With mybook.Worksheets("Sheet1")
If .ProtectContents = True Then
.Unprotect Password:="Password"

.Range("F3").ClearContents
.Range("F3").Value = "=SUMIFS(Tab1!$W$26:$W$320,Tab1!$C$26:$C$320,""*""&F5&""*"")"

.Protect Password:="Password", AllowFiltering:=True, UserInterfaceOnly:=True, DrawingObjects:=False

Else
ErrorYes = True
End If
End With


If Err.Number > 0 Then
ErrorYes = True
Err.Clear
'Close mybook without saving
mybook.Close savechanges:=False
Else
'Save and close mybook
mybook.Close savechanges:=True
End If
On Error GoTo 0
Else
'Not possible to open the workbook
ErrorYes = True
End If

Next Fnum
End If

If ErrorYes = True Then
MsgBox "Unable to update all files:" _
& vbNewLine & "protected workbook/sheet or a sheet/range that not exist"
End If

'Restore ScreenUpdating, Calculation and EnableEvents
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub

 

[Joe4]

I don't understand. That section in red just looks like it is populating a formula to cell F3 that return a sum.
How are you using that to determine where to freeze panes?

 

[AxeCM]

I don't understand. That section in red just looks like it is populating a formula to cell F3 that return a sum.
How are you using that to determine where to freeze panes?

Hi Joe,
I currently use the Macro above to make various changes to worksheets across multiple reports at once.
The section in Red, is the bit i re-write depending on the change required. E.g. Changing formulas, tables, formatting etc.
At the moment it's just showing the last change i applied. So will need to be deleted and replaced with the code for changing the Freeze Pane.

 

[Joe4]

Without seeing your data and how you are using this, I am afraid it doesn't make much sense to me. Can you show us some data, how you are using it, and what you want to happen?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.