Find Page numbers on one sheet (footer) and paste on cells in a different sheet

[sn79]

Hello,
Please see the attached picture. I have a Sheet7 where I have page numbers in the footer (picture not attached). Now the numbers under column A (1.01, 1.02...) in sheet TableofContents (PGnumber.jpg attachment) are also in Sheet7 under column A. So I'm trying to get a macro running which would look up for values under column A in TableofContents sheet against Sheet7, and register the correct page number (from Sheet7) which belongs to the number (1.01, 1.02..) under column G or H in sheet TableofContents sheet. I tried mixing up a bunch of codes I found, but I am not very good at that. Please see below. Is there any way this can be used with minor modification?

[vba]


Sub AssignPageNumber()

Dim B As Integer, C As Integer
Dim Rang As Range, RT As Integer
RT = 2
On Error GoTo Quit

While RT < 5
For Each Rang In Range("A1:A200").SpecialCells(xlCellTypeFormulas, 1)
For B = 1 To .HPageBreaks.Count
Sheets("Sheet7").HPageBreaks(B).Location = Sheets("TableofContents").Cells(3, 7).Value
Sheets("Sheet7").VPageBreaks(C).Location = Sheets("TableofContents").Cells(3, 8).Value
Next Rang
Next B
Next C
Wend

Quit:
End Sub

[/vba]


Please help! This is driving me crazy

[SamT]

[vba]Sub InsertPageBreaksBeforeMatchingValues()
Dim Cel As Range, Found As Range, X As PageBreak, Cnt As Long 'Cnt stands for "Count"

With Sheets("Sheet7")
'Remove all existing H page breaks
For Each X In .HPageBreaks
X.Remove
Next X

For Each Cel In Sheets("TableOfContents").Range("A1:A" & Range("A1").End(xlDown).Row)
'How it works: "& Range("A1").End(xlDown).Row" returns the last row number in "A" that
'is not empty, then appends the Row number to the partial address ""A1:A".

Found = .Range("A:A").Find(Cel.Value)
'How it works: Find return a range only if the value is found. The Dot before the
'Range object forces the Range to belong to the "With Sheets" Sheet

If Not Found Is Nothing Then
'How it Works: If the value was not found, then the If Statement is not entered.

Cnt = Cnt + 1
'How it works: All numeric variables are initialized to 0.
'Every time a matching Cel Value is found, Cnt is incemented by 1.

'If Cnt > 1 Then
'Sow it works: If Cnt is only = 1, then the If Statement is not entered and no PageBreak is added.

.HPageBreaks.Add Before:=Found
'End If 'For Cnt
End If 'For Found
'About the "Cnt" Comments: IF you uncomment thew two "Cnt" lines, then the first
'instance of "Found" will NOT have a Page Break before it.

Next Cel
End With
End Sub
[/vba]

[sn79]

Hello SamT,
It throws a compile error and says user defined type not defined

[SamT]

Try declaring X as a Variant, instead of as a PageBreak.