sn79
06-21-2013, 09:25 AM
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?
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 :banghead:
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?
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 :banghead: