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?

 

[primuspaul]

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)

I diagnosed it wrong. It's not the spaces, it's the multiple sheets. Spaces are ok. Even multiple references to the same sheet is ok.

 

[tygrrboi]

I just dont think its possible to name a range like that... If it is, I do not know how.

 

[primuspaul]

I just dont think its possible to name a range like that... If it is, I do not know how.

I don't get it. All I want to do is put "Range1" in cell L1 and then to print whatever cells are listed under the "Refers To" column for that particular "Name." It works, just not when a range contains cells from multiple excel sheets.

 

[tygrrboi]

	A	B	C	D
1	Range1	Sheet1!A1:A5
2	Range2	Sheet1!B60:X70	Sheet2!A50:A52	'Sheet 3'!D10:E15
3	Range3	'Sheet 3'!C1:C20

<tbody>
</tbody>

Why not Make a Helper table in a different sheet

Then use that to discover your ranges

Match to find the range you typed into L2 Then loop through the following columns in that row and print the range if its not blank.

 

[primuspaul]

	A	B	C	D
1	Range1	Sheet1!A1:A5
2	Range2	Sheet1!B60:X70	Sheet2!A50:A52	'Sheet 3'!D10:E15
3	Range3	'Sheet 3'!C1:C20

<tbody>
</tbody>

Why not Make a Helper table in a different sheet

Then use that to discover your ranges

Match to find the range you typed into L2 Then loop through the following columns in that row and print the range if its not blank.

That's kind of what I'm doing given that the way I wanted to do it appears to be impossible.

 

[tygrrboi]

Sub PRANGE()
Dim LookUpRow As Integer, i As Integer


LookUpRow = Application.Match(Sheets("something").Range("L2").Value, Sheets("Lookup").Range("A:A"), 0) 
'change Sheets("something") to the sheet where you input in L2...
 
For i = 2 To 10 'change the last number based on the maximum number of sheets any named range could contain
    If Sheets("Lookup").Cells(LookUpRow, i).Value <> "" Then
        Sheets(Replace(Split(Sheets("Lookup").Cells(LookUpRow, i).Value, "!")(0), "'", "")).Select
        ActiveSheet.Range(Split(Sheets("Lookup").Cells(LookUpRow, i).Value, "!")(1)).PrintOut Preview:=True
    End If
Next i


End Sub

 

[primuspaul]

Sub PRANGE()
Dim LookUpRow As Integer, i As Integer


LookUpRow = Application.Match(Sheets("something").Range("L2").Value, Sheets("Lookup").Range("A:A"), 0) 
'change Sheets("something") to the sheet where you input in L2...
 
For i = 2 To 10 'change the last number based on the maximum number of sheets any named range could contain
    If Sheets("Lookup").Cells(LookUpRow, i).Value <> "" Then
        Sheets(Replace(Split(Sheets("Lookup").Cells(LookUpRow, i).Value, "!")(0), "'", "")).Select
        ActiveSheet.Range(Split(Sheets("Lookup").Cells(LookUpRow, i).Value, "!")(1)).PrintOut Preview:=True
    End If
Next i


End Sub

I have an excel cell with the following value:

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

How do I print that? What is the VB command? I am getting some errors.

 

[tygrrboi]

If all your ranges are in one cell seperated by commas like the example you gave me you can do this

Sub PRANGE()
Dim LookUpRow As Integer, i As Integer
Dim rPart As Variant




LookUpRow = Application.Match(Sheets("something").Range("L2").Value, Sheets("Lookup").Range("A:A"), 0)
'change Sheets("something") to the sheet where you input in L2...

rPart = Split(Sheets("Lookup").Cells(LookUpRow, 2).Value, ",")




 
For i = 0 To UBound(rPart)
        Sheets(Replace(Split(rPart(i), "!")(0), "'", "")).Select
        ActiveSheet.Range(Split(rPart(i), "!")(1)).PrintOut Preview:=True
Next i




End Sub

 

[primuspaul]

If all your ranges are in one cell seperated by commas like the example you gave me you can do this

Sub PRANGE()
Dim LookUpRow As Integer, i As Integer
Dim rPart As Variant




LookUpRow = Application.Match(Sheets("something").Range("L2").Value, Sheets("Lookup").Range("A:A"), 0)
'change Sheets("something") to the sheet where you input in L2...

rPart = Split(Sheets("Lookup").Cells(LookUpRow, 2).Value, ",")




 
For i = 0 To UBound(rPart)
        Sheets(Replace(Split(rPart(i), "!")(0), "'", "")).Select
        ActiveSheet.Range(Split(rPart(i), "!")(1)).PrintOut Preview:=True
Next i




End Sub

Subscript out of range error.

 

[tygrrboi]

What is written in your lookup table next to the range name you are looking up. Can you copy the full address you typed in



It might be because you put a space after the comma separating the sheets.