Print sequence # based on cell value + multiple copies. All tied to range to print from another cell.

[FrEaK_aCcIdEnT]

The code below has been peiced together and was functioning properly at one point. I can't for the life of me figure out why it wont work anymore.

I need it to do a few things.

1. Print multiple copies of a spreadsheet with the sequence number increasinging in cell "M1" on each print.
2. Based on how much of the "SRI" is filled out, print off the number of pages found in cell "J41" that is tied to a print range for for values of "1";"2;"3".

The Sequence # start is "H1"

It used to work in its current format. I was using the inputbox to determine the number of copies. I figured it would be easy to adjust it to accept x= ("M11") to make it cell based. Couldnt figure out how to deal with the left over "String" Tried "Integer" and came up with more issues. Been looking online for 2 days for something similar or that would help rack my brain to the correct modification of code...

Currently my problems with this code are... It will only print the 1st page. if there is a "1" in cell "J41". If it is a "2" or a "3" it acts like "End If"... No printing of the specified ranges.

Not sure what I did... If anyone gets a chance and wants to take a crack at it, please do. I will keep working on it. Possibly start on a clean slate with a list of what do I want it to do...

I also would like to force Ctrl+P to print. Disable the toolbar button and file menu selection. I know that goes in the workbook and in the sheet1 code. I had that to at one point, but couldnt get it to function either. Deleted it.

Thanks for any help y'all can give!

Sub PrintSequence()
' Keyboard Shortcut: Ctrl+p'
Dim x As String
Dim SequenceNumber As Long
Dim UniqueCopies As Integer
SequenceNumber = ActiveSheet.Range("H1").Value
x = InputBox("How many Routers need a copy of the SRI?", "How Many Copies?", "1")
If Val(x) < 1 Then Exit Sub
For UniqueCopies = 1 To x
ActiveSheet.Range("H1").Value = SequenceNumber
Sheets("SRI").Select
If Range("J41").Value = 1 Then
    Range("$A$1:$BV$44").PrintOut Copies:=1, Collate:=True
            SequenceNumber = SequenceNumber + 1
        If Range("J41").Value = 2 Then
            Range("$A$1:$BV$86").PrintOut Copies:=1, Collate:=True
                    SequenceNumber = SequenceNumber + 1
                If Range("J41").Value = 3 Then
                    Range("$A$1:$BV$128").PrintOut Copies:=1, Collate:=True
                            SequenceNumber = SequenceNumber + 1
                End If
        End If
End If
Next
End Sub

 

[Snakehips]

FrEaK,

A couple of things wrong with your code......

You have your J41 Ifs nested such that if J41 =1 it will print the smallest range then fail the J41=2 and J41=3 tests.

If J41 = 2 or 3 it will fail the J41=1 test and not then look at the two inner tests.

You need to change it to 3 separate tests as below or to use Else If structure.

If Range("J41").Value = 1 Then
    Range("$A$1:$BV$44").PrintOut Copies:=1, Collate:=True
    SequenceNumber = SequenceNumber + 1
 End If
 
 If Range("J41").Value = 2 Then
      Range("$A$1:$BV$86").PrintOut Copies:=1, Collate:=True
      SequenceNumber = SequenceNumber + 1
End If
 
If Range("J41").Value = 3 Then
Range("$A$1:$BV$128").PrintOut Copies:=1, Collate:=True
SequenceNumber = SequenceNumber + 1
End If

Alternatively use Select Case as per my final code below.

Also, if you are using the InputBox to get your value of x then you must use

Val(x) in

For UniqueCopies = 1 To Val(x)

Try the following code......
Uses select case rather than Ifs.
Shows an alternative if you wish to determine number of copies from value of cell M1 rathert than the InputBox.
Does not select the SRI sheet but uses a 'With' statement in the code.

Hope that helps.

Sub PrintSequence()
' Keyboard Shortcut: Ctrl+p'
Dim x As String
Dim SequenceNumber As Long
Dim UniqueCopies As Integer
Dim MyRange As Range
SequenceNumber = ActiveSheet.Range("H1").Value
x = InputBox("How many Routers need a copy of the SRI?", "How Many Copies?", "1")
If Val(x) < 1 Then Exit Sub
For UniqueCopies = 1 To Val(x)
 
'***** Alternative if Cell M1 value rather than InputBox.....
'x = ActiveSheet.Range("M1").Value
'If x < 1 Then Exit Sub
'For UniqueCopies = 1 To x
'*****
 
ActiveSheet.Range("H1").Value = SequenceNumber
' *** No need to select sheet SRI unless you really need to
With Sheets("SRI")
Select Case .Range("J41").Value
Case 1
MyRange = "$A$1:$B$44"
Case 2
MyRange = "$A$1:$B$86"
Case 3
MyRange = "$A$1:$B$128"
End Select
.Range(MyRange).PrintOut Copies:=1, Collate:=True
SequenceNumber = SequenceNumber + 1
End With
Next
End Sub

 

[FrEaK_aCcIdEnT]

FrEaK,

A couple of things wrong with your code......

You have your J41 Ifs nested such that if J41 =1 it will print the smallest range then fail the J41=2 and J41=3 tests.

If J41 = 2 or 3 it will fail the J41=1 test and not then look at the two inner tests.

You need to change it to 3 separate tests as below or to use Else If structure.

If Range("J41").Value = 1 Then
    Range("$A$1:$BV$44").PrintOut Copies:=1, Collate:=True
    SequenceNumber = SequenceNumber + 1
 End If
 
 If Range("J41").Value = 2 Then
      Range("$A$1:$BV$86").PrintOut Copies:=1, Collate:=True
      SequenceNumber = SequenceNumber + 1
End If
 
If Range("J41").Value = 3 Then
Range("$A$1:$BV$128").PrintOut Copies:=1, Collate:=True
SequenceNumber = SequenceNumber + 1
End If

Alternatively use Select Case as per my final code below.

Also, if you are using the InputBox to get your value of x then you must use

Val(x) in

For UniqueCopies = 1 To Val(x)

Try the following code......
Uses select case rather than Ifs.
Shows an alternative if you wish to determine number of copies from value of cell M1 rathert than the InputBox.
Does not select the SRI sheet but uses a 'With' statement in the code.

Hope that helps.

Sub PrintSequence()
' Keyboard Shortcut: Ctrl+p'
Dim x As String
Dim SequenceNumber As Long
Dim UniqueCopies As Integer
Dim MyRange As Range
SequenceNumber = ActiveSheet.Range("H1").Value
x = InputBox("How many Routers need a copy of the SRI?", "How Many Copies?", "1")
If Val(x) < 1 Then Exit Sub
For UniqueCopies = 1 To Val(x)
 
'***** Alternative if Cell M1 value rather than InputBox.....
'x = ActiveSheet.Range("M1").Value
'If x < 1 Then Exit Sub
'For UniqueCopies = 1 To x
'*****
 
ActiveSheet.Range("H1").Value = SequenceNumber
' *** No need to select sheet SRI unless you really need to
With Sheets("SRI")
Select Case [COLOR=blue][B][U].Range[/U][/B][/COLOR]("J41").Value
Case 1
MyRange = "$A$1:$B$44"
Case 2
MyRange = "$A$1:$B$86"
Case 3
MyRange = "$A$1:$B$128"
End Select
.Range(MyRange).PrintOut Copies:=1, Collate:=True
SequenceNumber = SequenceNumber + 1
End With
Next
End Sub

Thanks for the help!! I did remove the input box and replaced it with the alternative line of code.

I get a compile error "Invalid or unqualified reference" at the Blue portion of code.

I was not familure with Select Case. Most of my issues revolve around not knowing what my options are.

 

[Snakehips]

FrEaK,

Sorry about that.

Try editing to

Select Case ActiveSheet.Range("J41").Value

 

[FrEaK_aCcIdEnT]

FrEaK,

Sorry about that.

Try editing to

Select Case ActiveSheet.Range("J41").Value

Tried that right after I posted. Then got the same for .Range further down. Fixed that one as well.

Test ran it and got Run Time Error 91. Object variable or With Block variable not set.

Here is what I have in there so we are on the same page.

Sub PrintSequence()
' Keyboard Shortcut: Ctrl+p'
Dim x As String
Dim SequenceNumber As Long
Dim UniqueCopies As Integer
Dim MyRange As Range
SequenceNumber = ActiveSheet.Range("H1").Value
x = ActiveSheet.Range("M1").Value
If x < 1 Then Exit Sub
For UniqueCopies = 1 To x
ActiveSheet.Range("H1").Value = SequenceNumber
' *** No need to select sheet SRI unless you really need to
With Sheets("SRI")
Select Case ActiveSheet.Range("J41").Value
Case 1
MyRange = "$A$1:$B$44"
Case 2
MyRange = "$A$1:$B$86"
Case 3
MyRange = "$A$1:$B$128"
End Select
ActiveSheet.Range(MyRange).PrintOut Copies:=1, Collate:=True
SequenceNumber = SequenceNumber + 1
End With
Next
End Sub

 

[Snakehips]

FrEaK,

Firstly, note that the code assumes that the cells H1, M1 & J41 are all on the ActiveSheet from which you call the print routine.

I note that after testing on a reduced print range I did not restore your print ranges to $BV but left as $B.

Also because you had declared your variables, I, last minute, declared the variable MyRange. However, I wrongly declared it as a Range which is the cause of the second Error 91. This is now ok with the MyRange variable declared correctly as a String!! Hence you do not need ActiveSheet in the 5th line up from bottom.

I hope that there are no more silly mistakes on my part.

Try this....

Sub PrintSequence()
' Keyboard Shortcut: Ctrl+p'
Dim x As String
Dim SequenceNumber As Long
Dim UniqueCopies As Integer
Dim MyRange As String  '******!!!!!!!!!!!!
SequenceNumber = ActiveSheet.Range("H1").Value
x = ActiveSheet.Range("M1").Value
If x < 1 Then Exit Sub
For UniqueCopies = 1 To x
ActiveSheet.Range("H1").Value = SequenceNumber
With Sheets("SRI")
Select Case ActiveSheet.Range("J41").Value
Case 1
MyRange = "$A$1:$BV$44"  '***  !! B is now BV
Case 2
MyRange = "$A$1:$BV$86"
Case 3
MyRange = "$A$1:$BV$128"
End Select
.Range(MyRange).PrintOut Copies:=1, Collate:=True  '!!!!!!!!!!!
SequenceNumber = SequenceNumber + 1
End With
Next
End Sub

 

[FrEaK_aCcIdEnT]

No worries on the silly mistakes, I've been dealing with them every time I try to write something. I spend forever on it thinking I got it,only to beg for help. Then when I get it right, I sit back and ask why I didn't do that the 1st time... I am very appreciative of the help you gave.

Works great by the way!!!