creating a macro to print all sheets on dropdown list

[winona123]

I am doing this for a sports team. trying to speed up the printing process. normally if I have to print a specific sheet of workouts. lets say sheet 2, I have to go back to sheet 1 click on the dropdown list choose a new athlete then go back to sheet 2 and print. when teams get really big this takes awhile

I am trying to create a Macro that allows me to print sheet 2 but also prints it for every athlete on the drop down list.

can anyone guide me on how to do this if it is possible.

I have seen similar macros on here but haven't been able to change the code to work for my data

[spitfireblue]

Hi,

Here you go. I have tried to explain each step.
You can set it to run when you press print by setting the following code in ThisWorkbook in VBA

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Let me know if you need any more help.

[winona123]

This is great and i'm trying to figure it out, but code isn't really something i'm good at so some of it i'm lost on.

can you show me what it should end up looking like?

[winona123]

Okay so I did get it going. The only problem is once I run it, all of the cells with data and equations in them become [#N/A] and it prints that way. all of the equations and data are the same but that is what I get instead of values. and I can't get those cells to go back to being values

[spitfireblue]

Maybe double check that the link to 2016 DUP.xlsx is still working correctly and that your formula's are correct.
The code is pulling each athlete from Sheets("Data").Range("A2:A21") so you will need to replace each row in column A with the athlete's name that the formula is looking up in 2016 DUP.xlsx

[winona123]

Okay yes that was on my end I fixed it and it works great, thank you for helping me out with this.
one last question if I save the program will the Macro also save and always be there to use? Or is there a way to permanently save a Macro so I can use in on any excel file I pull up?

[spitfireblue]

It will only save to the file that it is in.
If you want it to be available for other workbooks setup the macro in a blank sheet and save it as a .xlam file in C:\Users\(Your User Name)\AppData\Roaming\Microsoft\AddIns\
Then in Excel Options under Addins, select "Go" and find the Addin that you have created and ensure it is ticked and then it should be available every time you open Excel.

[winona123]

Thank you very much for helping me out with this.