Macro for printing from dropdown menu

%3CLINGO-SUB%20id%3D%22lingo-sub-1324387%22%20slang%3D%22en-US%22%3EMacro%20for%20printing%20from%20dropdown%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1324387%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Afternoon%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20print%20pick%20sheets%20for%20employees.%3C%2FP%3E%3CP%3EI%20have%20already%20created%20a%20template%20of%20a%20picksheet%20in%20a%20worksheet%20and%20the%20sheet%20is%20generated%20using%20drop%20down%20menu%20of%20depot%20name%20and%20vlookups.%3C%2FP%3E%3CP%3ENow%20i%20need%20a%20macro%20that%20is%20able%20to%20print%20the%20Picksheets%20of%20all%20Depots%20listed%20on%20the%20drop%20down%20list%20all%20at%20once%2C%20instead%20of%20selecting%20a%20depot%20one%20at%20a%20time%20then%20print.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1324387%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1324626%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20for%20printing%20from%20dropdown%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1324626%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F624220%22%20target%3D%22_blank%22%3E%40marc47555%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20code%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-css%22%3E%3CCODE%3ESub%20PrintOutAllDepots()%0A%20%20%20%20Dim%20r%20As%20Range%0A%20%20%20%20Set%20r%20%3D%20Sheets(%22Orders%22).Range(%22D4%3AP4%22)%0A%20%20%20%20Dim%20i%20As%20Integer%0A%20%20%20%20i%20%3D%201%0A%20%20%20%20Sheets(%22Pallet%20Card%22).Activate%0A%20%20%20%20%0A%20%20%20%20For%20Each%20c%20In%20r%0A%20%20%20%20%20%20%20%20Range(%22R1%22).Value%20%3D%20r(1%2C%20i).Value%0A%20%20%20%20%20%20%20%20ActiveWindow.SelectedSheets.PrintOut%20Copies%3A%3D1%0A%20%20%20%20%20%20%20%20i%20%3D%20i%20%2B%201%0A%20%20%20%20Next%20c%0A%20%20%20%20%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1325058%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20for%20printing%20from%20dropdown%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1325058%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3Bthank%26nbsp%3B%20you%20so%20much%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Good Afternoon,

 

I need to print pick sheets for employees.

I have already created a template of a picksheet in a worksheet and the sheet is generated using drop down menu of depot name and vlookups.

Now i need a macro that is able to print the Picksheets of all Depots listed on the drop down list all at once, instead of selecting a depot one at a time then print.

2 Replies
Highlighted

@marc47555

 

Hi,

 

Please try this code:

Sub PrintOutAllDepots()
    Dim r As Range
    Set r = Sheets("Orders").Range("D4:P4")
    Dim i As Integer
    i = 1
    Sheets("Pallet Card").Activate
    
    For Each c In r
        Range("R1").Value = r(1, i).Value
        ActiveWindow.SelectedSheets.PrintOut Copies:=1
        i = i + 1
    Next c
    
End Sub

 

Hope that helps

Highlighted

@Haytham Amairah thank  you so much