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

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

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

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.



Alex wrote:

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

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


--

Dave Peterson