Automaically print a training sheet using names from a list

[JamesT1]

Good Afternoon folk

I use a sheet to keep track of staff training which is updated monthy or when required. I use a dropdown list to select the name which then automatically showns that individuals required training.

Is there a way I can automatically print each individual sheet with the name and required training by using a macro. I have tried to record a macro for it but it doesn't work correctly.

many thanks

Jim

[Domski]

So you want to automatically print a sheet for each name in your list?

Dom

[JamesT1]

Hi Dom

Yes,,, what I would like is to select the first name on the list,, this will then automatically insert the training.. press a button which will print the sheet, then the system automatically inputs the next name on the list print the sheet, then next name etc....

unless it is easier to press the button and the system automatically starts at the first name

currently selecting a name automatically inputs the required training using ' vlookup ' I don't think this needs to be changed

[Domski]

Okay...

As an example this will loop through the A1:A10 on Sheet2 and put each value in A1 on Sheet1 and then print Sheet1.

Please Login or Register  to view this content.

If you want more specific code can you either upload a sample worksheet or describe the layout of your workbook.

Dom

[JamesT1]

Hi Dom

Thanks for that,, hopefull the attached sheet will explain what I need.... I have inserted your code into module 1

Jim

[Domski]

This would do it for your example:

Please Login or Register  to view this content.

If the list of names starts in I5 it will automatically pick up the new range of names as long as it's a contiguous list.

Dom

[royUK]

Try this

Please Login or Register  to view this content.

[JamesT1]

Dom/Roy

Many thanks for your help, both these did what I need with a couple of small exceptions (nothing to do with your code, it worked perfectly on the example, Roy I had to remove the first 'End With' line (below .PrintOut) for it to work....

Both codes printed the sheet per name and then blank copies for the cells below them, which had equation in them

OK, why wouldn't it work in my live sheet and how did I resolve it??? (hope this help other in the future)

All to do with my lack of knowledge in 'Named Ranges' (its getting better), amongst other things

Cell C3 refers to a column with equations to show names or leave cells blank depending on the contents of the reference cells, The resulting list in Cell C3 showes blanks below the names because it picks up the cells with the equation, even although they are blank..(I didn't know that)

Eventually I created a Dynamic Named Range for cell C3 to refer too. In the 'Refers to' box, for the range, type:

=OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1)

N.B. This only works for cells with text in them, it stops at blanks and cells with numbers

I then inserted equations to refer to the column where the original list is. I recorded a macro to copy the list where the equations are then paste the values in the Dynamic Names Range', this then produced the list in C3 without the blanks.

The attached sheet shown the method I used. I don't know if there is a better way to do it,,, it works for me

Many thanks for both your help with this,, it set me on the road to solving it and increase my understanding of excel