+ Reply to Thread
Results 1 to 2 of 2

VBA to Print a Variable Quantity of Sheets with Page Numbers

Hybrid View

  1. #1
    Registered User
    Join Date
    07-25-2019
    Location
    San Diego, CA
    MS-Off Ver
    16.0.11727.20222
    Posts
    34

    VBA to Print a Variable Quantity of Sheets with Page Numbers

    Hi all,

    I have no idea where to start with this one, but I am trying to print 3 sheets in a workbook while maintaining the page numbers on the sheets. This is simple enough if I only need one of each sheet, but sometimes I'll need 2 or more copies of one sheet or another. I have been able to print the sheets using cell references to identify quantities, but I'm having trouble maintaining page numbers.

    For example, if I need:

    3 copies of Sheet 1
    2 copies of Sheet 2
    4 copies of Sheet 3

    I need 9 pages total, and would need the copies of Sheet 1 to say 1 of 9, 2 of 9, 3 of 9, then Sheet 2 as 4 of 9, 5 of 9 and so on.

    However, when I print, I only 1 of 3, 2 of 3, and 3 of 3 on Sheet 1, 2 and 3 respectively, before it starts again at 1 with the next round.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,651

    Re: VBA to Print a Variable Quantity of Sheets with Page Numbers

    Copy each sheet n times in a new workbook then print all the nine sheets from the new workbook.

    Range A1 has the number of copies per sheet.

    Sub n_Sheets_Print()
        
        Dim ws As Worksheet
        Dim wb As Workbook
        
        Application.ScreenUpdating = False
        
        For Each ws In ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
            For i = 1 To ws.Range("A1").Value
                If wb Is Nothing Then
                    ws.copy
                    Set wb = ActiveWorkbook
                Else
                    ws.copy After:=wb.Sheets(wb.Sheets.Count)
                End If
            Next i
        Next ws
        
        wb.Sheets.Select
        wb.Sheets.PrintOut
        
        wb.Close SaveChanges:=False
        Application.ScreenUpdating = True
                    
    End Sub
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Print one page xy times, page numbering only even (or odd) numbers
    By Ivan_5 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-24-2019, 11:31 AM
  2. How to print page numbers
    By daniellouwrens in forum Excel General
    Replies: 4
    Last Post: 10-03-2016, 05:17 AM
  3. Replies: 3
    Last Post: 08-28-2015, 08:06 AM
  4. VBA Print & Page numbers
    By lee2k60 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-21-2010, 11:01 AM
  5. Variable Print Titles on every printed page
    By StormyTheHornet in forum Excel General
    Replies: 0
    Last Post: 02-11-2010, 07:24 PM
  6. Replies: 4
    Last Post: 07-02-2008, 10:10 AM
  7. Print variable page sizes
    By nsv in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-01-2006, 06:07 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1