I have the following problem in Excel (OfficeXP/WindowsXP Version)

1) Workbook name is Equipment.xls.
2) Sheets are in pairs viz. PC, PCDetails, Printer, PrinterDetails,
Server, ServerDetails and so on....
3) C5 has Data Validation to show List = SheetName!ID on all the
sheets. SheetName!ID is a alpha numeric 11 digits xxx???xxxxx... xxx
are alphabets from A to Z and ??? is a 3 digit numbers which increments
as...111, 112, 113, 114 e.g KMC1114DTASM, KMC112DTASM.. and so on.
4) All Details Sheets are designed as a form where Cell C5 value is
used to extract values from its relevant pair sheet... eg PCDetails
will extract values from PC and PrinterDetails will extract values from
Printer and so on...
5) Range names for lookups are defined on each sheet...for PC sheet
PCID (IDs), for Printer Sheet as PrinterID and so on...
6) 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...
7) Range name for printing is also defined on each Sheet eg for PC
Sheet as PcDetailsPrint... etc..

My problem is that I need to print the form on the Details Sheet by
selecting one ID after another.., which is very time consuming...
sometimes I need to Print about 30 to 40 forms at a time...by selecting
one by one.

Mr. Dave Peterson was kind enough to give a prompt reply and suggested
the code shown below but it gives me an error as follows:

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

Mr. Peterson Code :

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(Prom¬pt:="Start with", _
Default:=1, Type:=1))
If StartVal = 0 Then
Exit Sub
End If
EndVal = CLng(Application.InputBox(Prom¬pt:="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("PCID").Value = iCtr
Application.Calculate 'just in case
wks.Range("PcDetailsPrint").Pr¬intOut preview:=True
Next iCtr
End Sub

Can somebody help me out... so that I can print in groups or may be
select from the Drop down box in C5 for groups... to print the sheets

Thanks in advance

Rashid Khan

Rashid,

first checkout that the code doesnot contain illegal characters:
(leftovers from copy paste perhaps)

set
option explicit

at the top of your module, then try to compile the code.

from your post i quote the lines that look like they contain
"leftovers". Note the "¬" character!


StartVal = CLng(Application.InputBox(Prom¬pt:="Start with", _
Default:=1, Type:=1))

EndVal = CLng(Application.InputBox(Prom¬pt:="End with", _
Default:=StartVal + 1, Type:=1))

wks.Range("PcDetailsPrint").Pr¬intOut preview:=True

if you still have problems tell us on which line it stops.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

Thanks for your reply.

1) I have set Option Explicit
2) thereare no leftovers.. I have checked each line.

It still shows the error.. but does not stop at any particular line...

Please guide me.

Thanks

Rashid Khan

In your previous thread, one of my suggestions was to use worksheet level names
to represent the input and range to print.

Have you thought of doing this?

prkhan56 wrote:

Thanks for your reply.

1) I have set Option Explicit
2) thereare no leftovers.. I have checked each line.

It still shows the error.. but does not stop at any particular line...

Please guide me.

Thanks

Rashid Khan

--

Dave Peterson

Hello Dave,
What you mean by worksheet level names? How to define that.
Can you give me a clue please?

I have range names defined for each sheet at present.

Pardon me for my ignorance and knowledge of Excel.. I am a sort of a
newbie

Thanks for your reply

Rashid Khan

When you do Insert|name|Define, you get a dialog where you can enter the name of
the range and what it refers to.

If you put the sheet name in that "names in workbook" box, like:

Sheet1!InputCell
or
'Sheet 22 of 23'!InputCell

Then these are sheet level (aka local) names.

After you do this, you can do another Insert|Name|define and see the sheet name
to the right of the name.

======
And the sheet names can all point to different cells on the different sheets.

I could have my inputcell in A1 or B3 or x99.

But in code, I could use:

msgbox worksheets("sheet1").range("inputcell").Value

And not care where the it was actually located.

=======

Then the code to print this kind of stuff would become much easier. Just plop a
button from the forms toolbar on the worksheet and run the same macro for all
the worksheets.

You can determine what worksheet you're on by seeing what button was clicked
(not important now).

======

If you work with names, do yourself a favor and get a copy of Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

prkhan56 wrote:

Hello Dave,
What you mean by worksheet level names? How to define that.
Can you give me a clue please?

I have range names defined for each sheet at present.

Pardon me for my ignorance and knowledge of Excel.. I am a sort of a
newbie

Thanks for your reply

Rashid Khan

--

Dave Peterson