Excel：如何仅使用一张纸打印唯一页面

Question 1

这是我使用的方法的答案： - 创建一个宏来自动更改单元格的值并调用打印机打印页面。代码的重要部分是循环和打印。

创建宏并运行它：

Sub PageNumber()
Application.Dialogs(xlDialogPrinterSetup).Show
For I = 1 To 50
ActiveSheet.Range("G28").Value = "Page " & I & " of 50"
ActiveSheet.Range("G28").Select
With Selection
    HorizontalAlignment = xlRight
    <insert all your cell formatting here>
End With
ActiveWindow.SelectedSheets.Printout Copies:=1, Collate:=True
Next
End Sub

Answer

这是我使用的方法的答案： - 创建一个宏来自动更改单元格的值并调用打印机打印页面。代码的重要部分是循环和打印。

创建宏并运行它：

Sub PageNumber()
Application.Dialogs(xlDialogPrinterSetup).Show
For I = 1 To 50
ActiveSheet.Range("G28").Value = "Page " & I & " of 50"
ActiveSheet.Range("G28").Select
With Selection
    HorizontalAlignment = xlRight
    <insert all your cell formatting here>
End With
ActiveWindow.SelectedSheets.Printout Copies:=1, Collate:=True
Next
End Sub

Question 2

我有一个宏，可以从数据库表中提取数据，并用该数据反复填充模板表。

数据导入模板

宏会填写模板并将每个模板保存为单独的文件或创建单独的工作表，由您选择。最后一部分可以调整以执行其他操作，这主要是为了演示一种从基于行的数据库填写表单的简单方法。

那里有一个示例文件，供您进行简单测试并开始使用。以下是该宏的主要内容：

Option Explicit

Sub FillOutTemplate()
'Jerry Beaucaire  4/25/2010
'From Sheet1 data fill out template on sheet2 and save
'each sheet as its own file.
Dim LastRw As Long, Rw As Long, Cnt As Long
Dim dSht As Worksheet, tSht As Worksheet
Dim MakeBooks As Boolean, SavePath As String

Application.ScreenUpdating = False  'speed up macro execution
Application.DisplayAlerts = False   'no alerts, default answers used

Set dSht = Sheets("Data")           'sheet with data on it starting in row2
Set tSht = Sheets("Template")       'sheet to copy and fill out

'Option to create separate workbooks
    MakeBooks = MsgBox("Create separate workbooks?" & vbLf & vbLf & _
        "YES = template will be copied to separate workbooks." & vbLf & _
        "NO = template will be copied to sheets within this same workbook", _
            vbYesNo + vbQuestion) = vbYes

If MakeBooks Then   'select a folder for the new workbooks
    MsgBox "Please select a destination for the new workbooks"
    Do
        With Application.FileDialog(msoFileDialogFolderPicker)
            .AllowMultiSelect = False
            .Show
            If .SelectedItems.Count > 0 Then    'a folder was chosen
                SavePath = .SelectedItems(1) & "\"
                Exit Do
            Else                                'a folder was not chosen
                If MsgBox("Do you wish to abort?", _
                    vbYesNo + vbQuestion) = vbYes Then Exit Sub
            End If
        End With
    Loop
End If

'Determine last row of data then loop through the rows one at a time
    LastRw = dSht.Range("A" & Rows.Count).End(xlUp).Row

    For Rw = 2 To LastRw
        tSht.Copy After:=Worksheets(Worksheets.Count)   'copy the template
        With ActiveSheet                                'fill out the form
            'edit these rows to fill out your form, add more as needed
            .Name = dSht.Range("A" & Rw)
            .Range("B3").Value = dSht.Range("A" & Rw).Value
            .Range("C4").Value = dSht.Range("B" & Rw).Value
            .Range("D5:D7").Value = dSht.Range("C" & Rw, "E" & Rw).Value
        End With

        If MakeBooks Then       'if making separate workbooks from filled out form
            ActiveSheet.Move
            ActiveWorkbook.SaveAs SavePath & Range("B3").Value, xlNormal
            ActiveWorkbook.Close False
        End If
        Cnt = Cnt + 1
    Next Rw

    dSht.Activate
    If MakeBooks Then
        MsgBox "Workbooks created: " & Cnt
    Else
        MsgBox "Worksheets created: " & Cnt
    End If

Application.ScreenUpdating = True
End Sub

Answer