This macro was created in Excel 2003 and works fine however once the tool containing this macro is converted to an xlsm format with Excel 2010 I get the run time error 6 where indicated.
Would anyone know how to fix this so it runs in Excel 2010 without issues?
Thanks in advance
Would anyone know how to fix this so it runs in Excel 2010 without issues?
Code:
Sub OTBPageBreak()
Dim thePB As Integer
Dim lastrow As Long
Dim therow As Long
Dim startrow As Long
Dim endrow As Long
wsOTB.Activate
ActiveSheet.Unprotect Password:="GRMRS"
wsOTB.PageSetup.PrintArea = "A4:AE370"
With wsOTB
.ResetAllPageBreaks
ActiveWindow.View = xlPageBreakPreview
'.VPageBreaks(1).DragOff Direction:=xlToRight, regionindex:=1
End With
thePB = 1
lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
startrow = 4
For therow = 4 To lastrow
Cells(therow, 2).Activate
If ActiveCell.EntireRow.Hidden = False Then
If wsOTB.HPageBreaks.Count = thePB Then
wsOTB.HPageBreaks(thePB).DragOff Direction:=xlDown, regionindex:=1
Else
endrow = therow
==== If Range("B" & startrow & ":B" & endrow).SpecialCells(xlCellTypeVisible).Count = 47 Then ***Run Time Error 6***
If ActiveSheet.HPageBreaks.Count < thePB Then
ActiveSheet.HPageBreaks.Add Range("A" & therow)
Else
Set ActiveSheet.HPageBreaks(thePB).Location = Range("A" & therow)
End If
startrow = therow
endrow = therow
thePB = thePB + 1
End If
End If
End If
Next
With wsOTB.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = False
End With
ActiveSheet.Protect Password:="GRMRS"
ActiveWindow.View = xlNormalView
ActiveSheet.DisplayPageBreaks = False
Thanks in advance