I have an excel spreadsheet with relevant data on as little as one
sheet up t0 forty or more sheets. I have a print macro that asked what
jobs I want to print but I will only print one page and then go to the
next job. If I have data on more than one sheet I need to print it
manually. I do have a formula on my first sheet that determines how
many sheets this job would require. Is there some way to add to my
code so it looks at column J of that particular job and prints out
that number of sheets before it goes to the next counter. In addition
sheet number 1 I do not need to print with this macro. I always print
sheet two onward. Currently with this macro it will only print the
active page.

I would like to be able to input all my data in sheet 1. Use my print
command button. Before it starts print it will look in column j of
that job to determine the number of sheets to print. And whet it start
print it should start with sheet number 2. For instance if job number
2 has the number 5 in column J it will print 5 sheets starting with
sheet number 2 (Sheets 2-6)

Here is my current vba code

Sub doprint()
'
' doprint Macro

Dim i As Integer
Dim oCell As Range


sname = InputBox("Start in Job Number?", " First Job to Print", 0)
sname2 = InputBox("Finish in Job Number?", " Last Job to Print",
0)

Range("I40").Select
ActiveCell.FormulaR1C1 = sname
Range("I41").Select
ActiveCell.FormulaR1C1 = sname2

For Counter = sname To sname2
Range("L5").Select
ActiveCell.FormulaR1C1 = Counter
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1,
Copies:=1, Collate _
:=True
Next Counter

End Sub

Thnaks to any input I get.

Pen,

I think your code can be more simple like :
Sub doprint()
'
' doprint Macro

Dim i As Integer
Dim oCell As Range

sname = InputBox("Start in Job Number?", " First Job to Print", 0)
sname2 = InputBox("Finish in Job Number?", " Last Job to Print",
0)

Range("I40").value = sname
Range("I41").value = sname2

For Counter = sname To sname2
Range("L5").value = Counter
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1,
Copies:=1, Collate :=True
Next Counter

End Sub

but don't know why you use oCell and i variable declaration without
using them ... ?

Thanks,,

Halim

Little Penny menuliskan:
I have an excel spreadsheet with relevant data on as little as one
sheet up t0 forty or more sheets. I have a print macro that asked what
jobs I want to print but I will only print one page and then go to the
next job. If I have data on more than one sheet I need to print it
manually. I do have a formula on my first sheet that determines how
many sheets this job would require. Is there some way to add to my
code so it looks at column J of that particular job and prints out
that number of sheets before it goes to the next counter. In addition
sheet number 1 I do not need to print with this macro. I always print
sheet two onward. Currently with this macro it will only print the
active page.

I would like to be able to input all my data in sheet 1. Use my print
command button. Before it starts print it will look in column j of
that job to determine the number of sheets to print. And whet it start
print it should start with sheet number 2. For instance if job number
2 has the number 5 in column J it will print 5 sheets starting with
sheet number 2 (Sheets 2-6)

Here is my current vba code

Sub doprint()
'
' doprint Macro

Dim i As Integer
Dim oCell As Range


sname = InputBox("Start in Job Number?", " First Job to Print", 0)
sname2 = InputBox("Finish in Job Number?", " Last Job to Print",
0)

Range("I40").Select
ActiveCell.FormulaR1C1 = sname
Range("I41").Select
ActiveCell.FormulaR1C1 = sname2

For Counter = sname To sname2
Range("L5").Select
ActiveCell.FormulaR1C1 = Counter
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1,
Copies:=1, Collate _
:=True
Next Counter

End Sub

Thnaks to any input I get.