macro to print template with diff IDs

S

supladaiii

New Member
Joined
Mar 15, 2008
Messages
30
I have a template that requires the user to input the ID# to pull up the record. I need to pull and print several records. I can name a range with the diff IDs I need on a separate sheet. Will someone please help with a macro that will go through the named range and populate the ID# then print the template one a time? Let's say that the ID# is required in A1 of Sheet 1 and the named range will be in Sheet 2 of the same worksheet.

Thank you in advance for all the help.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Assuming your range is named List:

Code: 
Sub Test()
    Dim Cell As Range
    For Each Cell In Worksheets("Sheet2").Range("List")
        With Worksheets("Sheet1")
            .Range("A1").Value = Cell.Value
            .PrintOut
        End With
    Next Cell
End Sub
 
Upvote 0
Andrew Poulsom said:
Assuming your range is named List:

Code: 
Sub Test()
    Dim Cell As Range
    For Each Cell In Worksheets("Sheet2").Range("List")
        With Worksheets("Sheet1")
            .Range("A1").Value = Cell.Value
            .PrintOut
        End With
    Next Cell
End Sub
Click to expand...
THANK YOU!

Is there a way wherein instead of a named range, I can just enter however many IDs I need without naming the range and the FOR loop with apply to all IDs entered in Sheet2 regardless of whether it's only 1 or 100?

I didn't realize that with the named range, I would have to define the range every single time to identify the number of times the template will print.

Thanks again for helping!
 
Upvote 0
I'm not sure I fully understand. You can use a range instead of a named range, eg:

For Each Cell In Worksheets("Sheet2").Range("A1:A10")
 
Upvote 0
Andrew Poulsom said:
I'm not sure I fully understand. You can use a range instead of a named range, eg:

For Each Cell In Worksheets("Sheet2").Range("A1:A10")
Click to expand...
WOW! Fast reply!

I'm looking for something like: if there's no ID entered in Sheet2 then it will end the Sub. But if there's data in the A:A cells, for as long as there's data in the cells, it will loop and print each ID's record.
 
Upvote 0
Like this?

Code: 
Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Set Sh = Worksheets("Sheet2")
    With Sh
        Set Rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    For Each Cell In Rng
        With Worksheets("Sheet1")
            .Range("A1").Value = Cell.Value
            .PrintOut
        End With
    Next Cell
End Sub
 
Upvote 0
Andrew Poulsom said:
Like this?

Code: 
Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Set Sh = Worksheets("Sheet2")
    With Sh
        Set Rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    For Each Cell In Rng
        With Worksheets("Sheet1")
            .Range("A1").Value = Cell.Value
            .PrintOut
        End With
    Next Cell
End Sub
Click to expand...
YES! YES! YES! This is perfect!!!

Thank you so much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
VBA to add title from formula to a save as name
Replies
0
Views
138
NKUNZI
N
R
  • Solved
Msbox VBA code when cell value is changed
2
Replies
18
Views
551
Ramadan2512
R
S
  • Solved
Button to print to PDF with custom file name & custom location
Replies
3
Views
622
mumps
mumps
L
  • Question Question
Macro to correct entries across multiple sheets
Replies
1
Views
203
Lux Aeterna
L
K
  • Solved
Code to search job id from multiple sources and update main spreadsheet.
2
Replies
10
Views
676
mumps
mumps

Forum statistics

Threads
1,224,516
Messages
6,179,231
Members
452,898
Latest member
Capolavoro009

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top