I have a macro which you attach to a command button on the Excel 'Gate Pass' worksheet.
Clicking this button finds the last entry and copies that entry from column A to column M inclusive
It pastes the cell values into cells AB2 to AN2
It then copies the semi-fixed date information in cell A9 to cell AA2
(AB1 to AN1 has the same headings as row 11 columns A to M, and has 'Date' in AA1).
The Pass - a document in MS Word named "Gate Pass.doc" for this example, contains text and links to the data in cells AA2 to AN2
To link the data, copy cell AA2
Goto the Word document and Paste Special - Paste Link in the required position. Various options are available - I think that Unformatted or RTF formatted are OK.
Repeat for each cell in the range AA2 to AN2
Save the Word document - if you have a different name to "Gate Pass.doc", change the name in the macro.
The Macro copies the last record, then it tells Word to update the fields, and then print Page 1.
The Word document must be open - (you could add some code to open it if it was not already open).
After printing the Pass, the cursor in the spreadsheet is moved to the first cell in the next empty row, ready for the next vehicle.
Finally the updated workbook is saved automatically.
Here is how to create a command button and add the code:
On the Gate Pass Worksheet, create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to 'Print Pass'
Right-click the button again and select View Code
In the code window that opens enter this:
Private Sub CommandButton1_Click()
Dim rngLast As Range
Dim appWord As Word.Application
'create the link to MS Word
Set appWord = Word.Application
On Error GoTo ErrHnd
'find last entry
Set rngLast = .Range("A65534").End(xlUp)
'adjust range to columns A to M and Copy the new range
'paste values and formats to fixed location
'now copy the semi-fixed data
'control Word document
With appWord.Documents("Gate Pass.doc")
.PrintOut Range:=wdPrintFromTo, From:="1", To:="1"
'put cursor onto next line
Application.CutCopyMode = False
'save the updated worksheet
Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them. Option explicit goes before Private Sub CommandButton1_Click().
Click Save from the Visual Basic Menu.
Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).
Click the 'Print Pass' button.
Note that the way that this code finds the last entry, is by searching upwards in column A, so there must not be any data in column A below the last record.
Printing: the default printer is used. Another named printer could be used by adding some more information to the print command.