You are here:

Excel/Macro to Populate Template


I have a column (L) of Journal Entry numbers on a Worksheet named "Import".  I would like to have a macro go down the list, copy each JE number to a cell called "JE_Num" on a Worksheet named Print, and then call a macro called "Print_Form.

My Print_Form macro is working fine, but I can't get a macro to work that will go down the list of numbers one at a time, populate the form, print the form, and then return to the list for the next number, and stop when the list is complete.

Can you help?




Assume the numbers start in cell L2  and there are no filled cells in column L below the list of numbers you want to use to print.  I use ThisWorkbook so I assume that the macro is in the same workbook as the sheets you describe.  The macro should be placed in a general module although everything is fully qualified.  

Sub PrintData()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, r2 As Range, cell As Range
Set sh1 = Worksheets("Import")
Set sh2 = Worksheets("Print")
Set r1 = sh1.Range("L2", sh1.Cells(sh1.Rows.Count, "L").End(xlUp))
Set r2 = ThisWorkbook.Names("JE_Num").RefersToRange
For Each cell In r1
 r2.Value = cell.Value
 Print_Form     ' call Print_Form macro
End Sub

Untested, but this should do what you want.

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 All rights reserved.