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