Help with Mr. Peterson's Code.. Print serially from a Sheet

[prkhan56]

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

 

[keepITcool]

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.

 

[prkhan56]

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]

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]

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]

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]

Hello Dave,
I have downloaded the NameManager.Zip file and come back to you on this
matter.

Thanks

Rashid Khan

 

[prkhan56]

Hello Dave,

I wish to thank you for teaching me how to define Local Range Names.

I have no Global Range names in my Workbook now.

I have 3 local names on all my sheets viz.. ID (Dynamic), Data
(Dynamic), and PrintArea (variable rows on each sheet)

But few problems have come ...where I need your help now.

1) I cannot use the local names for Data Validation...and when I try
the following in Data Validation List eg = PC!ID it says 'You may not
use reference to other worksheets for Data Validation criteria. How
can I rectify this?
2) Cell C5 on all the sheets was used in Data Validation...where
previously I had a drop down box which when selected used to populate
other relevant data using Vlookups.

Would you kindly guide me through... How can I achieve the drop down
box and print in groups or may be select from the Drop down box in C5
for groups... to print the sheets?

Thanks once again for all your time and help

Rashid Khan

 

[Dave Peterson]

I think I'd just create an additional workbook level name for the
data|validation.

You can have multiple names refer to the same range.

In fact, you can use the worksheet name and the characters ID to create the
validation range name.

PCID

Then you can use it in the code to get the range you need.

dim myRng as range
dim wks as worksheet
set wks = worksheets("PC")
set myrng = worksheets("datavalidationlists").range(wks.name & "ID")
....

 

[Dave Peterson]

Then I _think_ the code would boil down to:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

Set myRng = Worksheets("datavalidationlists").Range(wks.Name & "ID")

For Each myCell In myRng.Cells
wks.Range("ID").Value = myCell.Value
Application.Calculate 'just in case
wks.Range("DetailsPrint").PrintOut preview:=True
Next myCell

End Sub

Just put a button from the forms toolbar on each worksheet and assign the macro
to each button.

(Double check the range names in the code. I used ID and DetailsPrint.)

 

[prkhan56]

ok would try and get back to you.

Thanks

Rashid Khan

 

[prkhan56]

Hi Dave,
So now I have Workbook level name and Sheet level names eg PCID
(global) and ID (sheet level), for all my sheets.

As you suggested I have define all my range names beginning with the
worksheet name...eg PC has PCID, Printer has PrinterID and so on.

But what is "datavalidationlists". Is this a new range name?

Because I pasted the following code and it gives me "Runtime error
'9'
Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet
Set wks = ActiveSheet
Set myRng = Worksheets("datavalidationlist¬s").Range(wks.Name &
"ID")
For Each myCell In myRng.Cells
wks.Range("ID").Value = myCell.Value
Application.Calculate 'just in case
wks.Range("DetailsPrint").Prin¬tOut preview:=True
Next myCell
End Sub
And other thing which your previous code had is missing in the above
code ...where two Input boxes used to pop-up and ask for the beginning
and end number...

With my limited knowledge the above mentioned code would run and print
thru the complete list (which is what I don't require). I need to
print certain records only and not all at one time...

I was thinking of having a S.No. Cell at the top right hand corner
(outside the print area obviously) with a Vlookup to give me an
indication of the record I am currently on and then check the beginning
and end number of record needed for printing and then put the numbers
accordingly in the beginning and end Input box.

Would you be kind enough to look into this?
May be you could have some other expert thought on this!
Thanks for all the time and effort you have taken to help me out.

Rashid Khan

 

[Dave Peterson]

What is the name of the worksheet that contains all the data|validation lists?

I used "datavalidationlists" since I didn't know.

Your values still look like xxx###yyyyy?

If yes, maybe checking those ### to see if they're between the numbers you type
in:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet

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

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

Set wks = ActiveSheet

Set myRng = Worksheets("datavalidationlists").Range(wks.Name & "ID")
'xxx???yyyyy
For Each myCell In myRng.Cells
If IsNumeric(Mid(myCell.Value, 4, 3)) Then
If StartVal <= Val(Mid(myCell.Value, 4, 3)) _
And EndVal >= Val(Mid(myCell.Value, 4, 3)) Then
wks.Range("ID").Value = myCell.Value
Application.Calculate 'just in case
wks.Range("DetailsPrint").PrintOut preview:=True
End If
End If
Next myCell

End Sub

 

[prkhan56]

Hi Dave,

Sorry for all the trouble you have taken to help me...your code shows
the 'Start with' and 'End with' Input box and then gives an
error as follows:

Run Time error '9' - Subscript out of range.

I am again giving you a detail of my Workbook and Range Names.

1) Workbook Name: Equipment Inventory Details.xls
2) Sheet Names shown in pairs: PC/PC Form, Printer/Printer Form,
Monitor/Monitor Form .....and so on...
3) Local Range Names defined as ID and Data on all sheets (PC, Printer,
Monitor ....)
4) Local Range Names defined as PrintArea on all sheets (PC Form,
Printer Form, Monitor Form ....)
5) Global Range Name defined as PcID, PcData, PrinterID, PrinterData,
MonitorID, MonitorData....
6) All IDs are like xxx###yyyyy (where ### is numeric)

All xxxForm sheets are designed like a form and Cell C5 on these sheets
is used with conditional formatting to display the ID from relevant
sheet e.g PcID from PC Sheet ... PrinterID from Printer Sheet and so
on...

I need to print from a certain ID to certain ID...

FYI, the following code suggested by you is working for one of my
worksheet with 'no local sheet range names' it shows my counter
formula in Cell K1 ...as 1 then 2 then 3 .. but after the macro is
run... I loose my Vlookup formula in K1 (the counter cell) .. the
counter cell is defined outside the print area. Print Area is defined
from Row 2 onwards.

Your 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("PC DETAILS")
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
Next iCtr
End Sub

If with the above code ...I don't loose my Vlookup in Cell K1 then I
think that would also be sufficient my need...Hope I am clear now.

Thanks a lot once again for all the time and help.

Rashid Khan

 

[Dave Peterson]

First, if you're using the code that you posted, it isn't the most current.

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet

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

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

Set wks = ActiveSheet

Set myRng = Worksheets("datavalidationlists").Range(wks.Name & "ID")
'xxx???yyyyy
For Each myCell In myRng.Cells
If IsNumeric(Mid(myCell.Value, 4, 3)) Then
If StartVal <= Val(Mid(myCell.Value, 4, 3)) _
And EndVal >= Val(Mid(myCell.Value, 4, 3)) Then
wks.Range("ID").Value = myCell.Value
Application.Calculate 'just in case
wks.Range("DetailsPrint").PrintOut preview:=True
End If
End If
Next myCell

End Sub

Second, what worksheet holds the lists that you use for data validation?

Change "datavalidationlists" on this line to the name that holds all the data
validation lists.


Set myRng = Worksheets("datavalidationlists").Range(wks.Name & "ID")

 

[prkhan56]

Hi Dave,

The code I posted was given by you only...which works ...however other
codes do not work.. I just posted to give you an idea about it.

Sorry Dave, My Workbook name is Equipment Inventory Details.xls which
has many sheets as mentioned in my previous post. Each sheet has its
own data/validation....

Do you mean I have to have another sheet with all the
Data/Validation...that would not be possible as I keep on increasing my
data day by day on each individual sheets.

Kindly guide me through this now.

Thanks for all the effort you have taken to help me out.

You are a real help

Rashid Khan

 

[Dave Peterson]

Yeah, but that wasn't the most current version of the code.

If you have the validation lists on each worksheet, then give each range the
same name--make it a worksheet level name.

Then this line:

Set myRng = Worksheets("datavalidationlists").Range(wks.Name & "ID")
becomes:
Set myRng = wks.Range("datavalidationnamedrange")

Change datavalidationnamedrange to the name you used.

You may want to take a look at the sequence of posts and look at how the code
evolved.

 

[prkhan56]

Yeah, but that wasn't the most current version of the code.

If you have the validation lists on each worksheet, then give each range the
same name--make it a worksheet level name.

Then this line:

Set myRng = Worksheets("datavalidationlists").Range(wks.Name & "ID")
becomes:
Set myRng = wks.Range("datavalidationnamedrange")

Change datavalidationnamedrange to the name you used.

You may want to take a look at the sequence of posts and look at how the code
evolved.

Hi Dave,

I have following as worksheet level range names:
Data (dynamic - currently $E$2:$BN$255) - defined on PC Sheet
ID (dynamic - currently $E$2:$E$255) - defined on PC Sheet
PrintData (variable) - defined on PCDetails Sheet

This is how my codes look now:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet
Dim StartVal As Long
Dim EndVal As Long
Dim TempVal As Long
Dim iCtr As Long
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
Set wks = ActiveSheet
Set myRng = wks.Range("Data")
'xxx???yyyyy
For Each myCell In myRng.Cells
If IsNumeric(Mid(myCell.Value, 4, 3)) Then
If StartVal <= Val(Mid(myCell.Value, 4, 3)) _
And EndVal >= Val(Mid(myCell.Value, 4, 3)) Then
wks.Range("ID").Value = myCell.Value
Application.Calculate 'just in case
wks.Range("PrintData").PrintOut preview:=True
End If
End If
Next myCell
End Sub

I am running the macro from the PcDetails Sheet and I feel that the
following line is causing some trouble.

Set wks = ActiveSheet

Pardon me if I am wrong, but you are an expert to see if I am right.

Thanks once again for all the help you have rendered so far.

Rashid Khan

 

[Dave Peterson]

I the activesheet is pcdetails, then I don't see a problem with that line.

What goes wrong?

 

[prkhan56]

Hi Dave,
I get two input boxes...and after inputting the numbers I get the
following error.

I get Run time error '1004'
Application-defined or object-defined error.

Rashid Khan