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

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Call PrintWorkouts
End Sub

Sub PrintWorkouts()
    
    Dim ws As Worksheet, cell As Range 'Define variables
    
    Set ws = ActiveSheet 'Set the active sheet as the one to print
    With ws.PageSetup 'Ensure the print area and page setup is correct
        .PrintArea = ws.Range("A1").CurrentRegion.Address
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    
    For Each cell In Sheets("Data").Range("A2:A21") 'Loop through A2 to A21 on sheet data
        Sheets("Week 1").Range("A1").Value = cell.Value 'Place value into Week 1 A1
        Calculate 'Ensure that formulas recalculate
        ws.PrintOut 'Print Worksheet
    Next 'Loop to the next Athlete

End Sub

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.