VBA code to modify a macro

[rodonnell]

I have this macro in a set of workbooks:

Sub print_sch3()
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.PrintTitleRows = "8:$13"
.PrintTitleColumns = ""
End With
Application.Goto Reference:="Print_sch3_school"

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("schedule3").Select
Range("A8").Select
End Sub

There's an error in this code that I'd like to fix with a macro-there are too many workbooks to update manually. The line that needs to be updated is: .PrintTitleRows = "8:$13". It needs to change to: .PrintTitleRows= "$8:$13".

Can you suggest some code that I should use to make this correction?

Thanks

 

[Norie]

Why do you need to change that?

If I run the code with either "8:$13" or "$8:$13" the rows to repeat is set to $8:$13 when I check in the Page Layout dialog.

 

[rodonnell]

It's a two page print out. The headers rows display correctly on the first page, but not on the second.

 

[Johnny C]

If there's too many macros to make this change, could you just add this macro to your PERSONAL.XLSB file and add it to the quick access toolbar? Then it will work in any file you have open and active

 

[Johnny C]

if you want VBA to modify VBA you would need something like

Dim myBook As Workbook
Dim i
Dim j

For Each myBook In Application.Workbooks
For i = 1 To myBook.VBProject.VBComponents.Count
For j = 1 To myBook.VBProject.VBComponents(i).CodeModule.CountOfLines
If myBook.VBProject.VBComponents(i).CodeModule.Lines(j, 1) = ".PrintTitleRows = ""8:$13""" Then
myBook.VBProject.VBComponents(i).CodeModule.DeleteLines j
myBook.VBProject.VBComponents(i).CodeModule.InsertLines j, ".PrintTitleRows = ""$8:$13"""
End If
Next j
Next i
Next myBook

 

[rodonnell]

Thanks very much, I'll give that try.