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

D

Dave Peterson

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

prkhan56

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
 
D

Dave Peterson

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

prkhan56

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
 
D

Dave Peterson

Which line?
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
Click to expand...
 
P

prkhan56

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


How to check step by step

Rashid Khan
 
P

prkhan56

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
 
D

Dave Peterson

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????????
 
P

prkhan56

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
 
D

Dave Peterson

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

prkhan56

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
 
D

Dave Peterson

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

prkhan56

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
 
P

prkhan56

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
 
D

Dave Peterson

Then I'm at a loss.

I'd try the F8 again.
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
Click to expand...
 
P

prkhan56

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
 
D

Dave Peterson

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

prkhan56

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
 
D

Dave Peterson

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Print in continous serials from a Sheet 6
Rename Multiple Sheets - Help with Mr Dave Peterson's Code 2
Help with Mr. Dave Peterson's Code for Consolidating Many Sheets to One 2
Help with Hyperlink...Mr. Dave Peterson's Code 4
copy selected cells to new sheet. 12
Find dupes, list in MsgBox with cell.address convert ws wide code toone sheet 6
Help with Code to Copy to New Worksheet 2
Inaccurate count with code 6

Top