Choosing Range Names in VBA

[actjfc]

Hello Excel Warriors!

I have a workbook with let's say three sheets: A, B, and C. Within them each one has three different named print areas: let's say A1,A2, and A3 - B1, B2, and B3, C1, C2, and C3.

I would like to pass on to a kind of function or sub the variables "Sheet" and "Area", something like:

Sheet = Range("Z1")
Area = Range("Z2")

Sub MacroforPrinting(Sheet,Area)

Active.Sheet = Sheet
PrintArea = Area

With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub

Of course the above macro does NOT work. Can somebody help me out?

This sub will be inside a much larger code.

Thanks!

 

[Norie]

What's in Z1 and Z2?

Sub test()
    Sh = Range("Z1")
    Area = Range("Z2")
    MacroforPrinting Sh, Area
End Sub
Sub MacroforPrinting(Sh, Area)
With Worksheets(Sh)
    With .PageSetup
        .PrintArea = Area
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    .PrintOut Copies:=1, Collate:=True
End With
End Sub

 

[actjfc]

Thanks! I doing this:

Dim Area As String
Dim Sh As String

Sub MacroforPrinting()

If Range("Z2") = 3 Then
Sh = "Sheet1"
Area = "Schedule1"
MacroforPrinting Sh, Area
End If

End Sub

Sub MacroforPrinting(Sh, Area)
With Worksheets(Sh)
With .PageSetup
.PrintArea = Area
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
.PrintOut Copies:=1, Collate:=True
End With
End Sub

An error message says "ambigous name detected": MacroforPrinting

How do I fix it? Thanks again!

 

[Norie]

Eh, isn't it quite obvious what the problem is?

You've got 2 subs called MacroforPrinting - VBA ain't gonna like that.

 

[actjfc]

I changed it. However, it still does not work. I think the problem is in the definition of the variable "Area". It has a range name in it. Please, somebody help!

Dim Area As String
Dim Sh As String

Sub Test()

If Range("Z10") = 3 Then
Sh = "Sheet1"
Area = "RangeName"
MacroforPrinting Sh, Area
End If

End Sub

Sub MacroforPrinting(Sh, Area)
With Worksheets(Sh)
With .PageSetup
.PrintArea = Area
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
.PrintOut Copies:=1, Collate:=True
End With
End Sub