Excel: Macro to select item from Data Validation List, Print,...

[Gymnast14]

I have a data validation list of employees and a spreadsheet that "vlookups" and calculates several factors based on the employee ID number that is in the list. I have over 5,000 employees in the list.

My question is: Can I create a macro that will select an employee from the list, print the spreadsheet, then select the next name, print, select next name....etc etc.

I've tried the FAQ on MailMerge in excel and I just can't get it to work. So this is my way of trying to turn this into a "merge to printer" spreadsheet!

Thanks for any help!!

 

[SkipVought]

Hi,

You ought to have you LIST in a range on a sheet.

Reference the range in your Data > Validation - List.

Reference the same range for you macro.

dim r as range
for each r in YourSheetObject.YourListRangeObject
   SomeCellObject.Value = r.value
   YourSheetObject.PrintOut
next

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[Gymnast14]

Ok, can you help me a little more on the code part...

The tab name that contains my list, etc is called "Form" and my list range is called "myList".

So would it be:

dim r as range
for each r in Form.myList
SomeCellObject.Value = r.value
Form.PrintOut
next

What do put for "SomeCellObject.Value"...? Thanks for your help

 

[SkipVought]

dim r as range
for each r in Sheets("Form").Range("myList:)
   Sheets("Form").Cells(1, "A").Value = r.value
   Sheets("Form").PrintOut
next

puts the data in Form!A1

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[Gymnast14]

Ok, here is exactly what my code looks like. It doesn't like the "For Each r In Sheets("Form").Range("myList")" line.

Sub ListPrint()
Dim r As Range
For Each r In Sheets("Form").Range("myList")
Sheets("Form").Cells(1, "A").Value = r.Value
Sheets("Form").PrintOut
Next
End Sub

 

[SkipVought]

Do you have a range named myList that you can see in the Name Box?

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[Gymnast14]

Ha! That was the problem - the "myList" that the data validation was pointing to was on a different tab so of course the macro couldn't find it. Duh on my part.

I copied/pasted my list of employee ID's onto the Forms tab and renamed that "myList" and now it works like a charm. Thank you SOOOOO much. I really appreciate it. This will save me so much time and frustration. You are a genius!

 

[SkipVought]

A range WILL work on another sheet IF there is no other range also having that name that was inserted previously.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[xlbo]

For future reference, please post VBA questions in the VBA forum: Forum707

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question