Print in continous serials from a Sheet

[prkhan56]

Hello All Experts,

I am using OfficeXP and have the following problem:

I have many sheets for example Pc and PcDetails, Printer and
PrinterDetails....etc

PcDetails sheet is designed as a form which extracts data from Pc sheet
using various Vlookups. In cell C5 of PcDetails I have used a Range
Name 'PCList' (PcList is the ID numbers of various PCs) from the Pc
Sheet. All relevant details are shown according to the choice selected
in Cell C5.

My problem is that I have to print about 10 to 20 PcDetails sheet and I
have to choose it one after other.

I need a solution which should show me a InputBox where I can input the
starting number (ID) and the ending number (ID).. which would then
print the PcDetails one after other...the print range is
'PcDetailsPrint'. Please note that I always print in continuous
serial for eg.. 1 to 10, 15 to 30...and so on.

Any help would be very much appreciated.

TIA

Rashid Khan

 

[Dave Peterson]

So it's just populate the cell, let the vlookup's calculate and print?

If yes, how about this:

Option Explicit
Sub testme()

Dim StartVal As Long
Dim EndVal As Long
Dim TempVal As Long
Dim iCtr As Long

Dim wks As Worksheet

Set wks = Worksheets("PCDetails")

StartVal = CLng(Application.InputBox(Prompt:="Start with", _
Default:=1, Type:=1))
If StartVal = 0 Then
Exit Sub
End If

EndVal = CLng(Application.InputBox(Prompt:="End with", _
Default:=StartVal + 1, Type:=1))
If EndVal = 0 Then
Exit Sub
End If

If EndVal < StartVal Then
TempVal = StartVal
StartVal = EndVal
EndVal = TempVal
End If

For iCtr = StartVal To EndVal
wks.Range("pclist").Value = iCtr
Application.Calculate 'just in case
wks.Range("PcDetailsPrint").PrintOut preview:=True
Next iCtr

End Sub

 

[prkhan56]

Thanks for your quick reply Dave.

But when I run the code it shows me two Input Boxes and afterwards the
following error:

Run time Error 1004
Application-defined or Object-defined error

Am I doing something wrong?

Thanks for your time and help

Rashid

 

[Dave Peterson]

Maybe I made a typo--or copied a typo from your original post.

The worksheet is named:
PCDetails

The range names are:
pclist
PcDetailsPrint

That's where I'd check first.

If the names don't match up with the code, fix the code.

If the names don't exist, then add them with the same spelling as in the code.

======
If this doesn't help, post back which line is the problem line. (But I'm
betting you'll find it!)

 

[prkhan56]

Hi Dave,
I checked the code and the names match... but still get the run time
error 1004.
I think I have mixed up something.. I will try to make it clear.

1) Workbook name is Equipment.xls.
2) Sheets are in pairs viz. PC, PCDetails, Printer, PrinterDetails,
Server, ServerDetails and so on....
3) C5 on all the sheets is used for lookup to fill in data on the sheet
(all the Details sheet is designed like a form)
4) Range names for lookups are defined as for PC sheet PCList (IDs),
for Printer Sheet as PrinterList (IDs) and so on...
5) When I click on C5 .. it shows me a the ID List in a drop down
box...which when selected fill in the relevant details using various
Vlookup...
6) For your info ID is 11 digits xxx???xxxxx... xxx are alphabets from
A to Z and ??? is a number which increments as...111, 112, 113, 114
......

I need to print IDs say from 113 to 120...which now I do one by one...

I hope I have made it clear to you now.

Thanks for your help and time.

Rashid Khan

 

[Dave Peterson]

xxx???yyyyy scares me. How would a macro know which one to use?

Are the xxx and yyyyy always the same characters--no matter what the numbers
(???) are? If they're not, then I think you'll have to have a list of id's to
loop through. (This may be easy if those dropdowns are from data|Validation and
are located on another list.)

But I think I'd change the layout slightly.

Instead of using different range names (probably global) for each list (pclist,
printerlist, serverlist), just use the same name for each worksheet--but make it
a sheet level name. Don't forget to give the range to print the same sheet
level name, too.

And then we'd need some sort of cross reference between the data validation list
and the sheet/cell you're using.

Then you could plop a button from the forms toolbar and have the macro assigned
to it use the sheet that owns the button to deterimine what to print.

 

[prkhan56]

xxx???yyyyy scares me. How would a macro know which one to use?

Sorry for scaring you...I am a newbie and was thinking to keep a hard
copy with me with Row numbers and judge from the print which records I
need to print...do u get me

Are the xxx and yyyyy always the same characters--no matter what the
numbers
(???) are? If they're not, then I think you'll have to have a list of
id's to
loop through. (This may be easy if those dropdowns are from
data|Validation and
are located on another list.)

You are right here too...xxx and yyy are not always same characters.
I don't get what u mean by having a list of IDs to loop through and
Data Validation (pardon me for my knowledge). Now I am bit scared with
all these high level things..

But I think I'd change the layout slightly.

Instead of using different range names (probably global) for each list
(pclist,
printerlist, serverlist), just use the same name for each
worksheet--but make it
a sheet level name. Don't forget to give the range to print the same
sheet
level name, too.
And then we'd need some sort of cross reference between the data
validation list
and the sheet/cell you're using.
Then you could plop a button from the forms toolbar and have the macro
assigned
to it use the sheet that owns the button to determine what to print.

All this is above the scope of my knowledge. I have about 11 pairs of
Sheet and my requirement is to print a continuous range from the drop
down box ...
If required I can send u the worksheet because now I am more scared
then you are!
Thanks a lot once again for all the help and time of yours
Rashid Khan