Printing a Named Range across multiple sheets in a VBA Macro

    Hi, pretty new user here. I've been self teaching myself for a while now and am constantly using Google to try and find help and for the most part have done OK, but I'm coming up with blocks on this one.


    I have an excel file that based on the results of different checkboxes, I want it to print only certain sections of the document. I've done named ranges to set each print area, and have just a simple macro to start with to see if I could get it to work that looks something like this


    Range("Pre_PPAP").PrintOut Copies:=1, Collate:=True


    With "Pre_PPAP" being a named range of cells that covers 7 different tabs in the document. When I try to debug through the basically one line of code I get a runtime error 1004 global error.


    Haven't found anything that answers what I'm seeing. Is the problem with the named range or with the code in the macro?


    Thanks.

    Hello and Welcome to the Forum :)


    The question is basically : How have you defined your named range ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    If I understand correctly what you want, in the example above in the Name Manager, I've defined a range called Pre_PPAP that refers to the following:


    ='Sample Request Sheet'!$A$1:$J$48,'Process Control Sheet'!$A$1:$K$32,'Problem Report Sheet'!$A$1:$I$30,'Inspection Sheet'!$A$1:$N$31,'Process Flow'!$A$1:$G$46,'Launch #2'!$A$1:$J$30,'Launch #3'!$A$1:$I$24


    I have other named ranges similar to the one above that I want to use in a macro to print based on which check box initiates the macro for printing. I didn't want to get too complicated until I knew if printing the named range would work but I get the run time error.


    Thank you for the welcome!

    Most probably the error is generated by the fact you are not using the exact same range size for your seven sheets ...


    Seems to me you should test Union of your different ranges ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    I wondered at that myself, but didn't see anything online that would stop me from using different sized ranges. I'm unfamiliar with Union? Is that a function? My self training has never come across that one before.

    Quote from GCramer

    I wondered at that myself, but didn't see anything online that would stop me from using different sized ranges. I'm unfamiliar with Union? Is that a function? My self training has never come across that one before.


    Hello,


    Here is an example


    Code
    1. Union(Range("A1:B5"), Range("D6:C10")).Select


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Thanks Carim, I will give that a shot and see what I get.

    Union(Range('Sample Request Sheet'!$A$1:$J$48),Range('Process Control Sheet'!$A$1:$K$32),Range('Problem Report Sheet'!$A$1:$I$30),Range('Inspection Sheet'!$A$1:$N$31),Range('Process Flow'!$A$1:$G$44),Range('Launch #2'!$A$1:$J$30),Range('Launch #3'!$A$1:$I$24).Select


    It's coming back with errors on the ' to start the sheet name? It highlights the ' and says compile error: Expected: expression

    So I put the following in VBA and it doesn't give me errors, but not sure if it will print what I want it to print which is all the individual named ranges?


    Range ("Sample_Request"), Range("Process_Control"), Range("Problem_Report"), Range("Inspection_Sheet"), Range("Process_Flow"), Range("Launch_2"), Range("Launch_3")

    Hello,


    The Union instruction needs to be built as follows :

    Code
    1. Sub TestUnion()
    2. Union(Sheets("Sample Request Sheet").Range("A1:J48"), Sheets("Process Control Sheet").Range("A1:K32")).Select
    3. End Sub

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Carim, finding out I know a lot less than what I thought is not always a fun thing, but I do appreciate the help and I do not mind learning new things. Will see how this one works. Once I run the Union sub then the result of that becomes what so that I can print it out?

    Sub TestUnion()

    Union(Sheets("Sample Request Sheet").Range("A1:J48"), Sheets("Process Control Sheet").Range("A1:K32"), Sheets("Problem Report Sheet").Range("A1:I29"), Sheets("Inspection Sheet").Range("A1:N31"), Sheets("Process Flow").Range("A1:G46"), Sheets("Launch #2").Range("A1:J30"), Sheets("Launch #3").Range("A1:I24")).Select

    End Sub


    This is the whole macro, and I still get the same Run Time Error '1004":

    Method 'Union' of object'_Global' failed.


    I'm beginning to think it's an impossible task and that I should just rebuild the document from scratch on a single tab.

    Before changing your strategical approach to printing ...


    have you tested the following:


    Code
    1. Sub TestPrintUnion()
    2. Union(Sheets("Sample Request Sheet").Range("A1:J48"), Sheets("Process Control Sheet").Range("A1:K32"), Sheets("Problem Report Sheet").Range("A1:I29"), Sheets("Inspection Sheet").Range("A1:N31"), Sheets("Process Flow").Range("A1:G46"), Sheets("Launch #2").Range("A1:J30"), Sheets("Launch #3").Range("A1:I24"))..PrintOut Copies:=1, Collate:=True
    3. End Sub

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Carim, I did try the above and get the same Run-time error '1004':

    Method 'Union' of object'_Global' failed statement pop up as I have with every other way I've tried it. I honestly think there's probably something wrong with my file at this point.


    I've already rebuilt most of it in the timeframe on a single tab that I can just use the check boxes to 'hide' certain sections of the sheet for printing.