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

[sn79]

Hello,
Please see the attached pictures. I have a Sheet7 where I have page numbers in the footer. Now the numbers under column A (1.01, 1.02...) in sheet TableofContents (PGnumber.png attachment) are also in Sheet7 (as shown in sheet7.png attachment) 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?

#
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

#

Please help! This is driving me crazy

[sn79]

Any Ideas???

[sn79]

Any Ideas? I'm stuck!

[Martha]

Hi there,

I've just posted a similar question, before I found yours. Did you figure it out by chance?

Martha

[sn79]

Hi Martha,
Couldn't get any response from this or another forum. So I've myself started reading VBA. Here is the code I started

Please Login or Register  to view this content.

Issue is that I get a 1004 error. Somehow the same set of codes work in a different module perfectly. This link ( http://excel.tips.net/T003135_Page_Numbers_in_VBA.html ) has some code that throws the page number on a msgbox, but I thought my approach would be simpler (off course, only if I'm successful in coding it