Print area based on input

[primuspaul]

I want to print certain areas of various sheets in a workbook based on user input.

      Sub PAREA()
         Dim xy As String
         xy = .Cells(L, 2).Text
         Range(xy).PrintOut Preview:=True
      End Sub

The file has multiple Names and those names refer to various parts of the file. Some Names refer to multiple areas on multiple sheets and some of those sheets have names with spaces in them (which also caused problems in my tests).

What is the right way to do this? L2 on some Sheet1 would have text like PrintArea1 or PrintArea2 and PrintArea1 and PrintArea2 would also be the names of some of those print area names. How do I do this?

 

[tygrrboi]

sheet1!$A:$J,'sheet 2'!$A:$G should work
sheet1!$A:$J, 'sheet 2'!$A:$G will give an error

 

[primuspaul]

sheet1!$A:$J,'sheet 2'!$A:$G should work
sheet1!$A:$J, 'sheet 2'!$A:$G will give an error

sheet1!$A:$J,'sheet 2'!$A:$G

That is what is in the cell that contains the table-based, script-generated print area.

 

[MARK858]

@primuspaul, have you changed both Sheets("Lookup") and Sheets("something") to your actual sheet names?

 

[primuspaul]

@primuspaul, have you changed both Sheets("Lookup") and Sheets("something") to your actual sheet names?

I changed "something" to the actual sheet where the text value of the print range is, but what is Lookup supposed to be?

 

[MARK858]

See post #14 except now tygrrboi is expecting you to have your comma separated data in column B.

 

[primuspaul]

Okay, I solved it.

Sub PAREA()
On Error GoTo NoRange
Dim xy() As String
xy() = Split(Range("H2").Value, ",")

For Each element In xy()
    Application.Dialogs(xlDialogPrinterSetup).Show
    Worksheets(Split(element, "!")(0)).Range(Split(element, "!")(1)).PrintOut Preview:=True
Next element

Exit Sub


NoRange:
MsgBox "Error"
End Sub

Another box can contain this:

=COVER!$A:$J,='COVER 2'!$A:$J

But H2 must "sanitize" it with

=SUBSTITUTE(SUBSTITUTE(H1,"=",""),"'","")

to get this:

COVER!$A:$J,COVER 2!$A:$J

If it's not cleansed like that, it breaks with sheets that have spaces. The only problem I have now is it's inconvenient because you have to choose a printer each time and print once for every function. I will work on that on my own, but advice is welcome.

Yeah, it's a bit annoying. Even if I autoprint to a pdf for each print operation, it will still generate 2 pdfs. Any way to print everything to a single pdf?

 

[MARK858]

The only problem I have now is it's inconvenient because you have to choose a printer each time

Try commenting out the line below

Application.Dialogs(xlDialogPrinterSetup).Show

 

[primuspaul]

Try commenting out the line below

Application.Dialogs(xlDialogPrinterSetup).Show

Right now I use something a little different. What it does is it uses a different command (not printout()) to generate a pdf file for each step of the loop. The result is that you get a pdf popup for every print area range selected. The files are saved as 1.pdf, 2.pdf, etc... in the same directory as the excel file. It's like a print preview in practicality. I have two questions about this:

1. What I would like to do, what would be ideal, is if instead of opening up 5 pdf files for 5 print ranges, I got 1 pdf file containing the print ranges, one after the other.

2. If that is not practical, then I would at least like to save all of the pdf file inside a "temp" directory. I already know how to do this, but there is one problem: I get an error if that directory does not already exist. I'd like to save the pdfs to that directory, or if it does not exist, create it.

 

[tygrrboi]

Instead of printing or saving the ranges within the loop you can copy them to whatever location you want ,arranged on a new (temp) sheet. Then save that one temp sheet as a PDF after all desired ranges have been pasted on it. Clearing it when you're done.