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?

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("a 1").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 wrote:

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

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("t 4").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" wrote:

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("a 1").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 wrote:

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

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("t 4").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 wrote:

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("t 4").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" wrote:

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("a 1").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 wrote:

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


--

Dave Peterson

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" wrote:

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("t 4").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 wrote:

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("t 4").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" wrote:

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("a 1").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 wrote:

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


--

Dave Peterson

Not without a little help.

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

What line causes the error?

Alex wrote:

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" wrote:

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("t 4").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 wrote:

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("t 4").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" wrote:

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("a 1").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 wrote:

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


--

Dave Peterson


--

Dave Peterson