Printing ranges

[Alex]

I have four sheets of the same document on one worksheet. I have written a
basic formula that counts the sheets that have data in them and returns a
number 1 thru 4. I have also named four ranges 1 thru 4 to reflect sheets
1&2, / 1,2,& 3, /etc.

My problem now is, How can I print these only the ranges that this cell
calls for?

I can add a print button to my sheet but what would the macro look like to
accomplish this?

 

[Dave Peterson]

You can create a macro that looks at that cell with the formula and then tries
to print the range that's named in that cell.

I used A1 of sheet1 to contain the name--change it to what you need:

Option Explicit
Sub testme01()
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = ActiveWorkbook.Names(Worksheets("sheet1").Range("a1").Value) _
.RefersToRange
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Not a valid range!"
Exit Sub
End If

myRng.PrintOut preview:=True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Alex]

I tried what you said but had no luck. This is what I have. Any ideas what I
did wrong?

Private Sub CommandButton1_Click()
Option Explicit
Sub testme01()
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = ActiveWorkbook.Names(Worksheets("sheet1").Range("t4").Value) _
.RefersToRange
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Not a valid range!"
Exit Sub
End If

myRng.PrintOut preview:=True

End Sub

 

[Dave Peterson]

Use this code instead:

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = ActiveWorkbook.Names(me.Range("a1").Value) _
.RefersToRange
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Not a valid range!"
Exit Sub
End If

myRng.PrintOut preview:=True

End Sub

=======
I changed two things. First, I fixed your code so that it would work using a
commandbutton from the control toolbox toolbar.

And I changed this line:

Set myRng = ActiveWorkbook.Names(Worksheets("sheet1").Range("t4").Value) _
.RefersToRange

to:
Set myRng = ActiveWorkbook.Names(me.Range("a1").Value) _
.RefersToRange

This change assumes that the cell you're using to hold the name (A1) is on the
same sheet as the commandbutton.

 

[Alex]

Dave, Still no Luck. I copied your text directly but I keep getting a compile
error and the debug starts.
Any idea what that may be?

This is the code I entered from your message:
Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = ActiveWorkbook.Names(me.Range("a1").Value) _
.RefersToRange
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Not a valid range!"
Exit Sub
End If

myRng.PrintOut preview:=True

End Sub

 

[Dave Peterson]

Not without a little help.

Did you put the code behind the worksheet that has that commandbutton?

What line causes the error?

Dave, Still no Luck. I copied your text directly but I keep getting a compile
error and the debug starts.
Any idea what that may be?

This is the code I entered from your message:
Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = ActiveWorkbook.Names(me.Range("a1").Value) _
.RefersToRange
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Not a valid range!"
Exit Sub
End If

myRng.PrintOut preview:=True

End Sub

 

[Alex]

I selected the button, left clicked and selected view code. That is where I
put the code. Everything is on sheet 1.
The error comes on the "Dim myRng As Range" line, It is a Syntax error

 

[Dave Peterson]

There's nothing wrong with that line of code.

Maybe you got some extra characters when you pasted.

Try selecting that whole line and delete it. Then retype it.

I selected the button, left clicked and selected view code. That is where I
put the code. Everything is on sheet 1.
The error comes on the "Dim myRng As Range" line, It is a Syntax error