Need Help with printing multiple sheets Please

[Razzcul]

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.

 

[halimnurikhwan]

Hi RAzzcul,

try this :
Sub doprint()
'
' doprint Macro
Dim i As Integer, Counter, sname2
Dim oCell As Range
On Error GoTo Er
sname2 = InputBox("Start form sheet2 & Finish in Job Number?", "
Last Job to Print", 0)
Range("I41").Value = sname2
For Counter = 2 To sname2
Sheets(1).Range("L5").Value = Counter
Sheets(Counter).PrintOut From:=1, To:=1, Copies:=1,
Collate:=True
Next Counter
Er: Msgbox "Error occured, " & err.number & " - " & err.Description
End Sub

Note that counter is refer to sheets index that sould be printed and
assume that sheets you want to print is visible or unhide first if they
are hidden sheets

Rgds,

Halim
Razzcul menuliskan:

 

[Razzcul]

Hey thanks for your input. I did give that a try but I get a syntax
error

Here

sname2 = InputBox("Start form sheet2 & Finish in Job Number?", "

I starting to think this can't be done..


Thanks I'm open try try anything...........

 

[halimnurikhwan]

hi,
change:
sname2 = InputBox("Start form sheet2 & Finish in Job Number?", "

should be :
sname2 = InputBox("Start form sheet2 & Finish in Job Number?", " Last
Job to Print", 0)

that should typed in one line code ...

rgds,

halim


Razzcul menuliskan:

 

[Razzcul]

Let me try to explain a little better what I want the code to do>


Sub doprint()
'
' doprint Macro
' Macro recorded 9/25/2003 by AvilaJ

Dim i As Integer
Dim oCell As Range


In Colum A of first sheet Jobs are numbered 1,2,3 ect
In colums B-J is information specific to that job quanites piece
counts ect
On sheet 2 onward they use vlookup to get certain infomation fro
sheet 1
Cell D2 of sheet 2 onward had the job number which they get from L5 o
first sheet

sname = InputBox("Start in Job Number?", " First Job to Print", 0)
Asking me which job I want to start at for printing

sname2 = InputBox("Finish in Job Number?", " Last Job to Print", 0)
Which job I want to end at for printing

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

For Counter = sname To sname2

Range("L5").Select
Here it puts the first number in my range of jobs I want to print in L
on first sheet. Once it changes the number in L5 all the info on othe
pages change.
What I would like it to do is look up the value in column J of this jo
and print that number of sheets starting with sheet number 2. Fo
example if the value in column J of that job is 5 it will print
sheets starting with sheet number 2 (2-6)

ActiveCell.FormulaR1C1 = Counter
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
Collate _
:=True
Next Counter
Goto to the next job number and print

End Su

 

[halimnurikhwan]

Razzcul,

the things that make me confuse are :
1. Job?, what is job you mean, is it page or sheet or else ?
2. why you use sname while you know that the first sheet to print is
sheet2 or sheet with index = 2 ?
3. Column J is contain 65536 rows, just make sure me what row is column
J value with ?
4. Explain what is Job you mean !

explain in simple way what you want to achieve !

<smile>
Rgds,

Halim


Razzcul menuliskan:

 

[Razzcul]

The things that make me confuse are:

Q1. Job?, what is job you mean, is it page or sheet or else ?

A) Column A is the Job number 1,2,3. Incolum B-J contain information
speciftc to the Job. Quaintly, Box count and so on.. The job number is
just a reference number for all the vlookup formulrs I use. When That’s
where cel L5 comes in. When thaqt value is 2 all my vlookup forular will
use job number 2 (or and grab thew nessessary info from what ever is in
that row for example on sheet number 2 I use the to grab the job name.


=VLOOKUP(D2,Pieces!$A$5:$E$2394,2,0)
D2 is always = to the value of L5 on sheet 1. Job is just a term use
that is really a row of information referenced by the number in column
A.






Q2. why you use sname while you know that the first sheet to print is
sheet2 or sheet with index = 2 ?

A2) Without a doubt sname was a bad choice to use. Because sname has
nothing to do with the sheet name. I should have use ( jnumber)



Q3. Column J is contain 65536 rows, just make sure me what row is
column
J value with ?

A3)Column J contain a formula which figures out how many sheets need to
be printed for that particular Job or row







Q4. Explain what is Job you mean !

A4) Job is just a term use that is really a row of information
referenced by the number in column A. Columns a is the job reference
number column b-j is job information



Explain in simple way what you want to achieve!

As the counter goes through I what it to look at column J of that Row
and print that number of sheets starting with sheet 2.

Example
I run the macro and it ask me what job I want to start with. Let’s say
I type 1. Then it asks me want job I want to end with let say 3. This
means I want to print the information for Jobs 1,2,3. The number 1 is
in column A is in cell A5. The number 2 is in A6 and so on. So all the
information for job number 1 is in B5,C5,D5,E5,F5,G5,H5,I5 and J5. Job
number 2 is in B6,C6,D6,E6,F6,G6,H6,I6 and J6.

The key to sheets 2 onward is L5 on sheet1. L5 refers to the job
number. If L5 is 2 the sheet 2 onward will have information base on
cells B6,C6,D6,E6,F6,G6,H6,I6 and J6.

For Counter = sname To sname2 (In this example 1 to 3)
Range("L5").Select (The will but the first number in my counter
in cell L5. When a number is enter here all information on sheets 2
onward will change reflecting the row with that job number. For example
he first number in the range is 1. The macro put that value in L5. Since
sheets 2 onward use use cell D2 which is = cell L5 on the first sheet
there vlookup formulas will reflect the job of that reference
number(Job number).

Everything works great up to this point. The problem is it will only
print the active sheet I want to print a range of number of sheets
which could be different for each job. Here is where column J come in
because it contains the number of sheets that should be printed before
the counter goes to the next number (Job number or reference number)


In addition have changed the coed to use jnumber instead of sname.


Sub doprint()
'
' doprint Macro
' Macro recorded 9/25/2003 by AvilaJ

Dim i As Integer
Dim oCell As Range


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

Range("I40").Select
ActiveCell.FormulaR1C1 = Jummber1
Range("I41").Select
ActiveCell.FormulaR1C1 = Jummber2

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

End Sub


I hope this help I been at this for almost a week.

Thanks for your Help

 

[Razzcul]

Hope this helps