Sheet array based on cell value
 

Is it possible to creat a sheet array based on the value of a cell.

p = ActiveCell.Value

If p is 5 then I want to create a sheet array of 5 sheets and then
select them.

Currently I have to slect all 50 of my sheets. Which is fine excexpt if
I only print 5 page my header says 1 of 50, 2 of 50 ect. Being able to
custmun select my sheet would eliminate this problem


Here is my entire code.



Sub doprint()
'
' doprint Macro




Set h = ActiveWorkbook.Sheets
For Each sht In h
sht.Visible = True
Next


Dim i As Integer
Dim oCell As Range
Dim cCell As Range
Dim p As Long

strjobnumber = Application.InputBox("Start in Job Number?", " First
Job to Print", 0)
If strjobnumber = False Then

Sheets(Array("BatchSheet2", "BatchSheet3", "BatchSheet4", _
"BatchSheet5", "BatchSheet6", "BatchSheet7", "BatchSheet8",
"BatchSheet9", _
"BatchSheet10", "BatchSheet11", "BatchSheet12", "BatchSheet13",
"BatchSheet14", _
"BatchSheet15", "BatchSheet16", "BatchSheet17", "BatchSheet18",
"BatchSheet19", _
"BatchSheet20", "BatchSheet21", "BatchSheet22", "BatchSheet23",
"BatchSheet24", _
"BatchSheet25", "BatchSheet26", "BatchSheet27", "BatchSheet28",
"BatchSheet29", _
"BatchSheet30", "BatchSheet31", "BatchSheet32", "BatchSheet33",
"BatchSheet34", _
"BatchSheet35", "BatchSheet36", "BatchSheet37", "BatchSheet38",
"BatchSheet39", _
"BatchSheet40", "BatchSheet41", "BatchSheet42", "BatchSheet43",
"BatchSheet44", _
"BatchSheet45", "BatchSheet46", "BatchSheet47", "BatchSheet48",
_
"BatchSheet49", "BatchSheet50")).Select
ActiveWindow.SelectedSheets.Visible = False

Sheets("Pieces").Activate

Range("$A$1").Select

Exit Sub

End If



endjobnumber = Application.InputBox("Finish in Job Number?", " Last
Job to Print", 0)
If endjobnumber = False Then

Sheets(Array("BatchSheet2", "BatchSheet3", "BatchSheet4", _
"BatchSheet5", "BatchSheet6", "BatchSheet7", "BatchSheet8",
"BatchSheet9", _
"BatchSheet10", "BatchSheet11", "BatchSheet12", "BatchSheet13",
"BatchSheet14", _
"BatchSheet15", "BatchSheet16", "BatchSheet17", "BatchSheet18",
"BatchSheet19", _
"BatchSheet20", "BatchSheet21", "BatchSheet22", "BatchSheet23",
"BatchSheet24", _
"BatchSheet25", "BatchSheet26", "BatchSheet27", "BatchSheet28",
"BatchSheet29", _
"BatchSheet30", "BatchSheet31", "BatchSheet32", "BatchSheet33",
"BatchSheet34", _
"BatchSheet35", "BatchSheet36", "BatchSheet37", "BatchSheet38",
"BatchSheet39", _
"BatchSheet40", "BatchSheet41", "BatchSheet42", "BatchSheet43",
"BatchSheet44", _
"BatchSheet45", "BatchSheet46", "BatchSheet47", "BatchSheet48",
_
"BatchSheet49", "BatchSheet50")).Select

ActiveWindow.SelectedSheets.Visible = False

Sheets("Pieces").Activate

Range("$A$1").Select

Exit Sub

End If



Range("I40").Select
Range("I41").Select


For counter = strjobnumber To endjobnumber
Application.ScreenUpdating = False
Sheets("Pieces").Activate
Range("L5").Value = counter
Range("J85").Select
c = ActiveCell.Value

If c < 100 Then GoTo NextCounter

Range("J80").Select

p = ActiveCell.Value

Sheets(Array("BatchSheet1", "BatchSheet2", "BatchSheet3",
"BatchSheet4", _
"BatchSheet5", "BatchSheet6", "BatchSheet7", "BatchSheet8",
"BatchSheet9", _
"BatchSheet10", "BatchSheet11", "BatchSheet12", "BatchSheet13",
"BatchSheet14", _
"BatchSheet15", "BatchSheet16", "BatchSheet17", "BatchSheet18",
"BatchSheet19", _
"BatchSheet20", "BatchSheet21", "BatchSheet22", "BatchSheet23",
"BatchSheet24", _
"BatchSheet25", "BatchSheet26", "BatchSheet27", "BatchSheet28",
"BatchSheet29", _
"BatchSheet30", "BatchSheet31", "BatchSheet32", "BatchSheet33",
"BatchSheet34", _
"BatchSheet35", "BatchSheet36", "BatchSheet37", "BatchSheet38",
"BatchSheet39", _
"BatchSheet40", "BatchSheet41", "BatchSheet42", "BatchSheet43",
"BatchSheet44", _
"BatchSheet45", "BatchSheet46", "BatchSheet47", "BatchSheet48",
_
"BatchSheet49", "BatchSheet50")).Select

Sheets("BatchSheet1").Activate

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=p, Copies:=1,
Collate _
:=True

Application.ScreenUpdating = True

NextCounter:

Next counter

Sheets("Pieces").Activate

Range("$A$1").Select

Sheets(Array("BatchSheet2", "BatchSheet3", "BatchSheet4", _
"BatchSheet5", "BatchSheet6", "BatchSheet7", "BatchSheet8",
"BatchSheet9", _
"BatchSheet10", "BatchSheet11", "BatchSheet12", "BatchSheet13",
"BatchSheet14", _
"BatchSheet15", "BatchSheet16", "BatchSheet17", "BatchSheet18",
"BatchSheet19", _
"BatchSheet20", "BatchSheet21", "BatchSheet22", "BatchSheet23",
"BatchSheet24", _
"BatchSheet25", "BatchSheet26", "BatchSheet27", "BatchSheet28",
"BatchSheet29", _
"BatchSheet30", "BatchSheet31", "BatchSheet32", "BatchSheet33",
"BatchSheet34", _
"BatchSheet35", "BatchSheet36", "BatchSheet37", "BatchSheet38",
"BatchSheet39", _
"BatchSheet40", "BatchSheet41", "BatchSheet42", "BatchSheet43",
"BatchSheet44", _
"BatchSheet45", "BatchSheet46", "BatchSheet47", "BatchSheet48",
_
"BatchSheet49", "BatchSheet50")).Select

ActiveWindow.SelectedSheets.Visible = False

Sheets("Pieces").Activate

Range("$A$1").Select

ActiveWindow.ScrollRow = 1

End Sub