L
Little Penny
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
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