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.
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
Untested, but this should do what you want.
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