Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,253
Default Help with Mr. Peterson's Code.. Print serially from a Sheet


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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

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



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

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
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

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

Thanks

Rashid Khan

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

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

  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

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")
....



prkhan56 wrote:

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
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

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.)



Dave Peterson wrote:

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")
...

prkhan56 wrote:

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


--

Dave Peterson


  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

ok would try and get back to you.

Thanks

Rashid Khan

  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

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

  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

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 wrote:

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
  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

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

  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

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 wrote:

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


  #16   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

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

  #17   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

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 wrote:

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
  #18   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet



Dave Peterson wrote:
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 wrote:

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


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

  #19   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

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

What goes wrong?

prkhan56 wrote:

Dave Peterson wrote:
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 wrote:

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


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
  #20   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

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



  #21   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

I'm confused...

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

I thought you said all your data validation lists were on the worksheet that had
the data|validation cell.

I guess I still don't know where the ranges are for all the sheets.

If they're on the same sheet as the data validation cell, use the sheet level
names. If the list is on another sheet, then use the global name--but include
the name of the sheet with the data|validation (like in one of the earlier
versions).



prkhan56 wrote:

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


--

Dave Peterson
  #22   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

Hi Dave,
Thanks for the quick and prompt reply and sorry for the confusion...I
think now I am more confused than you are.

I am replying to each of your query:

I thought you said all your data validation lists were on the worksheet
that had
the data|validation cell.

Data Validation List is on PC Sheet and Data Validation Cell is on PC
Details Sheet.
I have used the Global Range name to show me the drop down.

I guess I still don't know where the ranges are for all the sheets.

As said in my previous post...I am working in sheet pairs...like PC
with Pc Details, Printer with Printer Details, Monitor with Monitor
Details

If they're on the same sheet as the data validation cell, use the sheet
level
names. If the list is on another sheet, then use the global name--but
include
the name of the sheet with the data|validation (like in one of the
earlier
versions).

But Data|Validation does not allow me to put something like
Allow - List - PC!ID

It says "you may not use reference to other worksheet for data
validation criteria"

The following is just an example of the PC sheet...there are many other
sheets... as I had mentioned in my previous post that they are all in
pairs (viz. PC goes with PC Details, Printer goes with Printer
Details...and so on)

Below is just an example of the PC Sheet:

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

Am I clear now?

Rashid Khan

  #23   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

Maybe it would be better to just use something like:

Set wks = ActiveSheet
select case lcase(wks.name)
case is = "pc"
set myRng = worksheets("pc details").Range("name1here")
case is = "printer"
set myRng = worksheets("printer details").Range("name2here")
case is = "whatever"
set myRng = worksheets("whatever details").Range("name3here")
end select

Include all your worksheet pairs.

prkhan56 wrote:

Hi Dave,
Thanks for the quick and prompt reply and sorry for the confusion...I
think now I am more confused than you are.

I am replying to each of your query:

I thought you said all your data validation lists were on the worksheet
that had
the data|validation cell.

Data Validation List is on PC Sheet and Data Validation Cell is on PC
Details Sheet.
I have used the Global Range name to show me the drop down.

I guess I still don't know where the ranges are for all the sheets.

As said in my previous post...I am working in sheet pairs...like PC
with Pc Details, Printer with Printer Details, Monitor with Monitor
Details

If they're on the same sheet as the data validation cell, use the sheet
level
names. If the list is on another sheet, then use the global name--but
include
the name of the sheet with the data|validation (like in one of the
earlier
versions).

But Data|Validation does not allow me to put something like
Allow - List - PC!ID

It says "you may not use reference to other worksheet for data
validation criteria"

The following is just an example of the PC sheet...there are many other
sheets... as I had mentioned in my previous post that they are all in
pairs (viz. PC goes with PC Details, Printer goes with Printer
Details...and so on)

Below is just an example of the PC Sheet:

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

Am I clear now?

Rashid Khan


--

Dave Peterson
  #24   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

Hi Dave,

It shows me two Input Boxes and then gives the following error:
Run-time error '91' Object variable or With block variable not set

This is the complete code 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
Select Case LCase(wks.Name)
Case Is = "pc"
Set myRng = Worksheets("pc details").Range("printarea")
Case Is = "printer"
Set myRng = Worksheets("printer details").Range("printarea")
Case Is = "monitor"
Set myRng = Worksheets("monitor details").Range("printarea")
End Select

'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("printarea").PrintOut preview:=True
End If
End If
Next myCell
End Sub

Rashid Khan

  #25   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

Which line?

prkhan56 wrote:

Hi Dave,

It shows me two Input Boxes and then gives the following error:
Run-time error '91' Object variable or With block variable not set

This is the complete code 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
Select Case LCase(wks.Name)
Case Is = "pc"
Set myRng = Worksheets("pc details").Range("printarea")
Case Is = "printer"
Set myRng = Worksheets("printer details").Range("printarea")
Case Is = "monitor"
Set myRng = Worksheets("monitor details").Range("printarea")
End Select

'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("printarea").PrintOut preview:=True
End If
End If
Next myCell
End Sub

Rashid Khan


--

Dave Peterson


  #26   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

HI Dave,
No particular line...
It is displayed in the centre of the VBE window


How to check step by step

Rashid Khan

  #27   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

Hi Dave,
Follow up to my previous post..I tried to Step Into with F8...but did
not succeed to see which line is causing the error

Rashid

  #28   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

I think you're gonna have to try again.

Maybe adding another check will help:

Select Case LCase(wks.Name)
Case Is = "pc"
Set myRng = Worksheets("pc details").Range("printarea")
Case Is = "printer"
Set myRng = Worksheets("printer details").Range("printarea")
Case Is = "monitor"
Set myRng = Worksheets("monitor details").Range("printarea")
Case else:
msgbox "design error with worksheet: " & wks.name
End Select

Maybe you didn't include all the worksheet pairs????????



prkhan56 wrote:

Hi Dave,
Follow up to my previous post..I tried to Step Into with F8...but did
not succeed to see which line is causing the error

Rashid


--

Dave Peterson
  #29   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

Hi Dave,
Sorry for all the trouble I have been giving you.

When I run the code... I get two Input boxes, then a message box saying
"design error with worksheet PC Details and after I click Ok on this
message box I get another error message in VBE saying:
Run-time error '91' Object variable or With block variable not set

I have total 8 Pairs (16 Sheets) + 1 Sheet for Party Code and 1 Sheet
for Eqpt Code - total 18 sheets.

I have included the 8 pairs in your code.

Following is the complete code 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
Select Case LCase(wks.Name)
Case Is = "pc"
Set myRng = Worksheets("pc details").Range("printarea")
Case Is = "printer"
Set myRng = Worksheets("printer form").Range("printarea")
Case Is = "monitor"
Set myRng = Worksheets("monitor form").Range("printarea")
Case Is = "switch"
Set myRng = Worksheets("switch form").Range("printarea")
Case Is = "router"
Set myRng = Worksheets("router form").Range("printarea")
Case Is = "firewall"
Set myRng = Worksheets("firewall form").Range("printarea")
Case Is = "modem"
Set myRng = Worksheets("modem form").Range("printarea")
Case Is = "scanner"
Set myRng = Worksheets("scanner form").Range("printarea")
Case Else:
MsgBox "design error with worksheet: " & wks.Name

End Select

'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("PRINTAREA").PrintOut preview:=True
End If
End If
Next myCell
End Sub

If you permit I can send you my file!

Thanks once again for all the support and help you have extended to me.

Rashid Khan

  #30   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

Change this section:

Case Else:
MsgBox "design error with worksheet: " & wks.Name

To:

Case Else:
MsgBox "design error with worksheet: " & wks.Name
exit sub

This won't fix your problem, but will stop the error from occurring.

But I thought your code should have been more like:

Case Is = "pc"
Set myRng = Worksheets("pc details").Range("validationrangenamehere!")

=========
And if you get this message:
design error with worksheet PC Details

That means that you're clicking on a button on PC Details. I thought that you
printed PC based on the data in PC details.

If you are printing directly from "PC Details", then you'll need to add that
"pair" to your list:

Case Is = "pc details"
Set myRng = Worksheets("whatworksheetgoeshere") _
.Range("validationrangenamehere!")

Don't forget to update the worksheet name and the range names to what matches
your project.


prkhan56 wrote:

Hi Dave,
Sorry for all the trouble I have been giving you.

When I run the code... I get two Input boxes, then a message box saying
"design error with worksheet PC Details and after I click Ok on this
message box I get another error message in VBE saying:
Run-time error '91' Object variable or With block variable not set

I have total 8 Pairs (16 Sheets) + 1 Sheet for Party Code and 1 Sheet
for Eqpt Code - total 18 sheets.

I have included the 8 pairs in your code.

Following is the complete code 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
Select Case LCase(wks.Name)
Case Is = "pc"
Set myRng = Worksheets("pc details").Range("printarea")
Case Is = "printer"
Set myRng = Worksheets("printer form").Range("printarea")
Case Is = "monitor"
Set myRng = Worksheets("monitor form").Range("printarea")
Case Is = "switch"
Set myRng = Worksheets("switch form").Range("printarea")
Case Is = "router"
Set myRng = Worksheets("router form").Range("printarea")
Case Is = "firewall"
Set myRng = Worksheets("firewall form").Range("printarea")
Case Is = "modem"
Set myRng = Worksheets("modem form").Range("printarea")
Case Is = "scanner"
Set myRng = Worksheets("scanner form").Range("printarea")
Case Else:
MsgBox "design error with worksheet: " & wks.Name

End Select

'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("PRINTAREA").PrintOut preview:=True
End If
End If
Next myCell
End Sub

If you permit I can send you my file!

Thanks once again for all the support and help you have extended to me.

Rashid Khan


--

Dave Peterson


  #31   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

Hi Dave,

The code works only for PC Details Sheet.

On other Sheets it gives me Run Time Error '1004'
'Application-defined or object-defined error'

I feel that the below line has to do something with it...because when I
checked the range names, ID is defined as a global name...though as you
had suggested ID is also a sheet level names on all the respective
sheets. May be this will give you some clue!

wks.Range("ID").Value = myCell.Value

This is the code 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
Select Case LCase(wks.Name)
Case Is = "pc details"
Set myRng = Worksheets("pc").Range("data")
Case Is = "printer form "
Set myRng = Worksheets("printer").Range("data")
Case Is = "monitor form"
Set myRng = Worksheets("monitor").Range("data")
Case Is = "switch form"
Set myRng = Worksheets("switch").Range("data")
Case Is = "router form"
Set myRng = Worksheets("router").Range("data")
Case Is = "firewall form"
Set myRng = Worksheets("firewall").Range("data")
Case Is = "modem form"
Set myRng = Worksheets("modem").Range("data")
Case Is = "scanner form"
Set myRng = Worksheets("scanner").Range("data")
Case Else:
MsgBox "design error with worksheet: " & wks.Name
Exit Sub
End Select

'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("PRINTAREA").PrintOut preview:=True
End If
End If
Next myCell
End Sub

I again thank you for all the help.

Rashid Khan

  #32   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

ID should be a sheet level name.

That makes this line:

wks.Range("ID").Value = myCell.Value

work for all the sheets that need to be printed.

If ID is a workbook level name, then wks.range("ID") won't exist except on the
worksheet that actually has that global name.

prkhan56 wrote:

Hi Dave,

The code works only for PC Details Sheet.

On other Sheets it gives me Run Time Error '1004'
'Application-defined or object-defined error'

I feel that the below line has to do something with it...because when I
checked the range names, ID is defined as a global name...though as you
had suggested ID is also a sheet level names on all the respective
sheets. May be this will give you some clue!

wks.Range("ID").Value = myCell.Value

This is the code 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
Select Case LCase(wks.Name)
Case Is = "pc details"
Set myRng = Worksheets("pc").Range("data")
Case Is = "printer form "
Set myRng = Worksheets("printer").Range("data")
Case Is = "monitor form"
Set myRng = Worksheets("monitor").Range("data")
Case Is = "switch form"
Set myRng = Worksheets("switch").Range("data")
Case Is = "router form"
Set myRng = Worksheets("router").Range("data")
Case Is = "firewall form"
Set myRng = Worksheets("firewall").Range("data")
Case Is = "modem form"
Set myRng = Worksheets("modem").Range("data")
Case Is = "scanner form"
Set myRng = Worksheets("scanner").Range("data")
Case Else:
MsgBox "design error with worksheet: " & wks.Name
Exit Sub
End Select

'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("PRINTAREA").PrintOut preview:=True
End If
End If
Next myCell
End Sub

I again thank you for all the help.

Rashid Khan


--

Dave Peterson
  #33   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

Hi Dave,

But when I remove the Range Name ID as a global name and keep it as a
sheet level name then I get the following error on PC Details Sheet.
Method 'Range' of object "_Worksheet' failed

And on other sheets (viz printer form, monitor form etc) it gives me
Run Time Error '1004' 'Application-defined or object-defined
error'
Now I have ID, Data, PrintArea as sheet level names but then the code
does not work.


Rashid Khan

  #34   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

If you don't say what lines cause the errors, I'd hesitate to guess.

prkhan56 wrote:

Hi Dave,

But when I remove the Range Name ID as a global name and keep it as a
sheet level name then I get the following error on PC Details Sheet.
Method 'Range' of object "_Worksheet' failed

And on other sheets (viz printer form, monitor form etc) it gives me
Run Time Error '1004' 'Application-defined or object-defined
error'
Now I have ID, Data, PrintArea as sheet level names but then the code
does not work.

Rashid Khan


--

Dave Peterson
  #35   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

Hi Dave,
But it does not stop on any particular line.. How could I find the
error..I tried with F8...but did not succeed

Rashid Khan



  #36   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

Then I'm at a loss.

I'd try the F8 again.

prkhan56 wrote:

Hi Dave,
But it does not stop on any particular line.. How could I find the
error..I tried with F8...but did not succeed

Rashid Khan


--

Dave Peterson
  #37   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

Hi Dave,
Sorry my sys was down...

I tried the code you suggested and as mentioned in my previous post...
if I remove ID as global range name then the code does not run...

Trying to run the code with F8 does not stop at any particular line.

You have taken so much trouble to help me upto now...please suggest
some solution. If you permit I can send you my file

Thanks once again

Rashid Khan

  #38   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

I think you'll have to find out why the code doesn't run. (I don't know what
that means.)



prkhan56 wrote:

Hi Dave,
Sorry my sys was down...

I tried the code you suggested and as mentioned in my previous post...
if I remove ID as global range name then the code does not run...

Trying to run the code with F8 does not stop at any particular line.

You have taken so much trouble to help me upto now...please suggest
some solution. If you permit I can send you my file

Thanks once again

Rashid Khan


--

Dave Peterson
  #39   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

Hi Dave,

What I meant is that if I have a global range name 'ID' then the code
runs only for a single sheet PC Details Sheet in this case... but when
I remove the global range name ID then even PC Details gives me run
time.

I think it has something to do with the Global and Worksheet Level
Name...I removed the global range name 'ID' and tested it on other
sheets then it works only for that particular sheet....

In other words... the code you suggested will not work unless there is
a Global Range name 'ID' defined...and will work only for the Sheet to
which the Global Range Name 'ID' is referred...Unfortunately I cannot
have more than one Global Range Name 'ID' for other sheets
Do you get what I am trying to say?

Thanks

Rashid Khan

  #40   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Help with Mr. Peterson's Code.. Print serially from a Sheet

Since the range named ID is on each of the sheets that needs to be printed
(that's the one cell that gets changed for each value in the data|validation
list), it should be a sheet level name.

In this line:
wks.Range("ID").Value = myCell.Value

wks is the activesheet. So excel should be able to find that ID range (if you
created it for that sheet).




prkhan56 wrote:

Hi Dave,

What I meant is that if I have a global range name 'ID' then the code
runs only for a single sheet PC Details Sheet in this case... but when
I remove the global range name ID then even PC Details gives me run
time.

I think it has something to do with the Global and Worksheet Level
Name...I removed the global range name 'ID' and tested it on other
sheets then it works only for that particular sheet....

In other words... the code you suggested will not work unless there is
a Global Range name 'ID' defined...and will work only for the Sheet to
which the Global Range Name 'ID' is referred...Unfortunately I cannot
have more than one Global Range Name 'ID' for other sheets
Do you get what I am trying to say?

Thanks

Rashid Khan


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Active cell counting in particular print page (one sheet having different print area) ananthmca2004 Excel Worksheet Functions 1 November 24th 05 12:29 PM
Why do I get a print error light trying to print an excel sheet ? SMC Excel Discussion (Misc queries) 2 November 5th 05 02:36 AM
Need Code To Print From Code Modules davidm Excel Programming 0 June 7th 05 06:11 AM
how to use Excel to generate alphabet serially Maxwell Excel Worksheet Functions 10 May 7th 05 03:37 PM
Help with Mr. Dave Peterson's Code for Consolidating Many Sheets to One Rashid Khan Excel Programming 2 July 31st 04 07:31 PM


All times are GMT +1. The time now is 10:38 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"