Help with XLM 4.0 Get Document command

[mcambrose]

The code below was submitted by Tom Ogilvy for getting the page breaks. It
works great, but I want to modify it to work on a set of sheets or just
replace "sheet1" with a variable name, so I can loop through various sheets.
I don't know how to modify the following xlm 4.0 macro command:
ThisWorkbook.Names.Add Name:="vPB", _
RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")"
so I can either use a variable in place of sheet1 so I can use this on
different sheets without having to set up a separate macro for each sheet.

Thanks


Sub PageBottomBorder()
Dim horzpbArray(), i As Integer, j As Integer
Dim verpbArray()
Dim mySheet As Object
Set mySheet = ActiveSheet
With mySheet.Range("wts_border_range").Borders(xlEdgeBottom)
.LineStyle = xlLineStyleNone
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

ThisWorkbook.Names.Add Name:="hzPB", _
RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")"
ThisWorkbook.Names.Add Name:="vPB", _
RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")"
i = 1
While Not IsError(Evaluate("Index(hzPB," & i & ")"))
ReDim Preserve horzpbArray(1 To i)
horzpbArray(i) = Evaluate("Index(hzPB," & i & ")")
With ActiveSheet.Rows(horzpbArray(i) - 1).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
i = i + 1
Wend
ReDim Preserve horzpbArray(1 To i - 1)
Debug.Print "Horizontal Pagebreaks (rows):"
For j = LBound(horzpbArray, 1) To UBound(horzpbArray, 1)
Debug.Print j, horzpbArray(j)
Next j


i = 1
While Not IsError(Evaluate("Index(vPB," & i & ")"))
ReDim Preserve verpbArray(1 To i)
verpbArray(i) = Evaluate("Index(vPB," & i & ")")
i = i + 1
Wend
'ReDim Preserve verpbArray(1 To i - 1)
' Debug.Print "Vertical Pagebreaks (columns):"
' For j = LBound(verpbArray, 1) To UBound(verpbArray, 1)
' Debug.Print j, verpbArray(j)
' Next j
End Sub

 

[Tom Ogilvy]

Sub PageBottomBorder()
Dim horzpbArray(), i As Integer, j As Integer
Dim verpbArray()
Dim mySheet As Object
Set mySheet = ActiveSheet
With mySheet.Range("wts_border_range").Borders(xlEdgeBottom)
.LineStyle = xlLineStyleNone
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

ThisWorkbook.Names.Add Name:="hzPB", _
RefersToR1C1:="=GET.DOCUMENT(64,""" & _
mySheet.Name & """)"
ThisWorkbook.Names.Add Name:="vPB", _
RefersToR1C1:="=GET.DOCUMENT(65,""" & _
mySheet.Name & """)"
i = 1
While Not IsError(Evaluate("Index(hzPB," & i & ")"))
ReDim Preserve horzpbArray(1 To i)
horzpbArray(i) = Evaluate("Index(hzPB," & i & ")")
With ActiveSheet.Rows(horzpbArray(i) - 1).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
i = i + 1
Wend
ReDim Preserve horzpbArray(1 To i - 1)
Debug.Print "Horizontal Pagebreaks (rows):"
For j = LBound(horzpbArray, 1) To UBound(horzpbArray, 1)
Debug.Print j, horzpbArray(j)
Next j


i = 1
While Not IsError(Evaluate("Index(vPB," & i & ")"))
ReDim Preserve verpbArray(1 To i)
verpbArray(i) = Evaluate("Index(vPB," & i & ")")
i = i + 1
Wend
'ReDim Preserve verpbArray(1 To i - 1)
' Debug.Print "Vertical Pagebreaks (columns):"
' For j = LBound(verpbArray, 1) To UBound(verpbArray, 1)
' Debug.Print j, verpbArray(j)
' Next j
End Sub