print loop macro with a twist(?)

[wrb]

I have tried, unsuccessfully, to create a print loop macro for a project despite the fact that it seems fairly straight forward.

I have a print template sheet whose data is derived by a vlookup from a 4 digit code. The problem is I have 500+ codes. I need a macro that can print the template page from a list of codes on a separate sheet and give enough time for the template page to populate with data.

Is there anyone who's done this before?

 

[Benjamin]

Hi,

I used the code below to do something very similar to what you are requesting. It uses a worksheet named Mail Form Data to populate 2 worksheets that act as form letters. The Mail Form Data tab has several columns of information, ie name, street address, ect, with a control number in column A. For each control number it finds in column A, it takes that value transfers it to a variable then activates the primary form letter page, inserts that value into a cell that is used by all the vlookups, it then uses the calculate statement to refresh all the vlookups on both form letter pages, then prints both pages, and moves to the next control number, and repeats the value transfer and refresh prior to printing the next set of form letters.

Public Sub Mailer2(ranget As String)


Sheets("Mail_Form_Data").Select  

Range(ranget).Select                  


For Each Cell In Selection

                    LookUp = Cell.Value       
                                  
                    Worksheets("Letter").Activate
                                       
                    Range("C2").Value = LookUp
                    
                    Calculate
                    
                    'Printing the sections of the mailer "Letter" 1st
                    Sheets("Letter").Select
                    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
                    
                    'Printing the sections of the mailer "Payroll Report" 2nd
                    Sheets("Payroll Report").Select
                    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
            
        Next Cell


Sheets("Controls").Select
Range("A1").Select

End Sub

I'll be at work for another hour, so if you have any questions fire them back.

Sincerely,

Benjamin