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]

is XY a userinput address? for example... do they type something like "B50:X60" into the cell?

Where do you get L... is that a global variable?

or are you trying to call the data input into Column L row 2? in which case it would be

xy = .Range("L2").Value

or

xy = .Cells(2, "L").Value

 

[primuspaul]

is XY a userinput address? for example... do they type something like "B50:X60" into the cell?

Where do you get L... is that a global variable?

or are you trying to call the data input into Column L row 2? in which case it would be

xy = .Range("L2").Value

or

xy = .Cells(2, "L").Value

No, that cell will contain the name of a print area, such as "PrintArea1." In the Name Manager, if you look at "PrintArea1," it will link to cells A:A on "sheet 5" and cells B5:H17 on "sheet 8."

 

[tygrrboi]

I have success with

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

When the named PrintArea includes multiple locations they print to separate sheets.

But for spaces... you cannot name a range with a space in it. Do you mean that the User might insert "PrintArea A" but you want the code to recognise that as "PrintAreaA"?

Use
xy = Replace(ActiveSheet.Cells(2,"L").Value, " ", "")

 

[primuspaul]

Sub PAREA()
         Dim xy As String
         xy = Range("L1").Value
         Range(xy).PrintOut Preview:=True
      End Sub

That works, but only with a Name that links to a print area that has sheets without spaces. Sheets named with something that has spaces fails with this error:

Run-time error '1004':

Method 'Range' of object '_Global' failed

 

[primuspaul]

I have success with

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

When the named PrintArea includes multiple locations they print to separate sheets.

That's what I want.

But for spaces... you cannot name a range with a space in it. Do you mean that the User might insert "PrintArea A" but you want the code to recognise that as "PrintAreaA"?

Use
xy = Replace(ActiveSheet.Cells(2,"L").Value, " ", "")

No, I mean some of my sheets have spaces in them. I might have "Sheet1" "Sheet2" and then a "Sheet 3." So "PrintAreaA" would contain parts of "Sheet 3." Then I'd get the error.

 

[tygrrboi]

I'm getting errors any time my named range contains multiple sheets. I do not know why.

 

[primuspaul]

I'm getting errors any time my named range contains multiple sheets. I do not know why.

No. It prints correctly when I select a named range that doesn't include sheet names with spaces, but as soon as I try to print a print area that includes sheet names with spaces, I get the error.

 

[tygrrboi]

Can you give me an example of the address of one of your named ranges without spaces that prints correctly and an example of the address of a named range that does not print (gives you an error)

 

[primuspaul]

I'm getting errors any time my named range contains multiple sheets. I do not know why.

So maybe that's what's happening to me. Multiple sheets are causing it and not necessary spaces in sheet names.