Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am trying to create a template in MS Excel to print similar pages but with an unique ID within each printed pages (e.g. ID: 001, 002 and so forth) but printing from one sheet only.

I am creating something like a logbook (i.e. each page is the same, only the UID is different)

Is there anyway to do so without duplicating the same sheet and insert the UIDs manually?

If the question is not clear I will rephrase. Thanks in advance!

share|improve this question
up vote 1 down vote accepted

This was the answer to my approach that I used: - Create a macro to change the values of the cell automatically and calls the printer to print the page. The important part of the code is the loop and printing.

Create the Macro and run it:

Sub PageNumber()
For I = 1 To 50
ActiveSheet.Range("G28").Value = "Page " & I & " of 50"
With Selection
    HorizontalAlignment = xlRight
    <insert all your cell formatting here>
End With
ActiveWindow.SelectedSheets.Printout Copies:=1, Collate:=True
End Sub
share|improve this answer

I have a macro for taking the data from a database sheet and filling out a template sheet over and over again with the data.

Data Into Template

The macro fills out the template and saves each one as a separate file OR creates a separate sheet, your choice. That last part can be tweaked to do other things, this is mainly to demonstrate a simple way to fill out a form from a row-based database.

There is a sample file there for simple testing and to get you going. Here's the main guts of that macro:

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"
        With Application.FileDialog(msoFileDialogFolderPicker)
            .AllowMultiSelect = False
            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
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
            ActiveWorkbook.SaveAs SavePath & Range("B3").Value, xlNormal
            ActiveWorkbook.Close False
        End If
        Cnt = Cnt + 1
    Next Rw

    If MakeBooks Then
        MsgBox "Workbooks created: " & Cnt
        MsgBox "Worksheets created: " & Cnt
    End If

Application.ScreenUpdating = True
End Sub
share|improve this answer
Hi thanks for the boilerplate of yours unfortunately it is not what I was looking for. EDIT: I solved mine by setting a macro to change a cell (i.e. increment the ID number and use macro to print it) – Alex Cheng Mar 26 '12 at 6:46
I would recommend you post your macro solution as a separate answer, then Accept your own answer. Cheers. – Jerry Beaucaire Mar 26 '12 at 15:22

I used Mail Merge in MS Word. Just put the day of week and date in a separate excel file, then copied my document to a word file, and used a letter mail merge to change the days and dates on each document. Very easy..

share|improve this answer
I’m confused.  Did you print multiple copies of a Word document, or of an Excel worksheet?  If the latter … how, exactly?  By copying it to a Word file?  Please explain. – G-Man May 27 '15 at 2:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .