C7 中的页码"1/3"，重复工作表中每页顶部的页码，Excel VBA

Question

我有一本工作簿，里面有几张纸。在每一张纸上，我在每一页上重复第1到13行。在单元格C7中，我希望页码和总页数为"0"的格式；1/3"；。

我发现这个VBA代码做的部分问题

将该部件放置在模块中

Sub PageNumber(MyRange As String)
Dim iVPC As Integer
Dim iHPC As Integer
Dim iVPB As VPageBreak
Dim iHPB As HPageBreak
Dim iNumPage As Integer
iHPC = 1
iVPC = 1
If ActiveSheet.PageSetup.Order = xlDownThenOver Then
iHPC = ActiveSheet.HPageBreaks.Count + 1
Else
iVPC = ActiveSheet.VPageBreaks.Count + 1
End If
iNumPage = 1
For Each iVPB In ActiveSheet.VPageBreaks
If iVPB.Location.Column > ActiveCell.Column Then Exit For
iNumPage = iNumPage + iHPC
Next
For Each iHPB In ActiveSheet.HPageBreaks
If iHPB.Location.Row > ActiveCell.Row Then Exit For
iNumPage = iNumPage + iVPC
Next
MyRange = "'" & iNumPage & "/" & Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
End Sub

将此部件放置在纸张对象窗口中

Private Sub Worksheet_Activate()
Dim StrString As String
Call PageNumber(StrString)
Range("C7:C7").Value = StrString
End Sub

我遇到的问题是，我需要iNumPage(当前页码，而不是总页数(为要打印的每个页面更新。有没有一种方法可以从页眉和页脚继承页码函数，并在Private Sub Workbook_BeforePrint中使用它，或者有其他解决方案。页码仅适用于单元格C7，因为打印输出中每页顶部的行1-13重复。

Answer 1

我在另一个网站上问过这个问题，我们已经尽可能接近了，但这不是一个好的解决方案，因为打印属性的处理，文档每页打印一次，如果你在两侧或pdf文件中打印什么，那会造成麻烦。

Sub Print_This_Sheet()
Dim i, iTot_pages
Dim lastrow As Long
Dim LastColumn As Long
Dim sht As Worksheet
Set sht = ActiveSheet
Dim LastColumStr As String
lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
LastColumn = sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column
LastColumStr = getColLet(LastColumn)
Application.EnableEvents = False                           ' no interference with workbook_BeforePrint !!!!
With ActiveSheet
.PageSetup.PrintArea = "A1:" & LastColumStr & lastrow
.PageSetup.PrintTitleRows = "$1:$13"
MsgBox "and other pagesetup-setting ....", vbInformation
iTot_pages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")     '----> normally the number of pages
For i = 1 To iTot_pages                               'loop through those pages
.Range("C7").Value = "'" & i & "/" & iTot_pages     'before printing, ajust C7
.PrintOut i, i, 1, True                          'PrintOut with previeuw page per page'print one page
Next
End With
Application.EnableEvents = True                            'enable events again
End Sub

Public Function getColLet(colNum As Long) As String
Dim i As Long, x As Long
'If Not isBetween(colNum, 1, Application.Columns.count) Then Exit Function
For i = Int(Log(CDbl(25 * (CDbl(colNum) + 1))) / Log(26)) - 1 To 0 Step -1
x = (26 ^ (i + 1) - 1) / 25 - 1
If colNum > x Then getColLet = getColLet & Chr(((colNum - x - 1)  26 ^ i) Mod 26 + 65)
Next i
End Function

这个解决方案的贡献者mcranmoss，这个函数来自以前的项目，所以它来自某个地方。。。。