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

K

keepITcool

use JKP's NameManager addin to verify you dont have "double scope"
range names.

e.g. name ID exists on a Worksheet level AND at workbook level.


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


Dave Peterson wrote :
 
P

prkhan56

Hi Dave,
ID is not a range name on the sheet which needs to be printed...but ID
and Data are on the sheet let's call it 'Main' Sheet for example.
The sheet which is to be printed let's call it 'Form' is using
the Global Name PCID to extract values. Because as mentioned in my
previous post I cannot use sheet level name 'ID' to get the drop
down box.

I used JKP's NameManager addin to verify my range names.

I have ID, Data, PrintArea as sheet level names only.

Your macro only works if there is a range name defined as ID
(Global)...otherwise it does not work...

I have tested this with all relevant sheets...If there is a global
range name ID then your macro works for that particular sheet only... I
have tested with each sheet.

Any suggestions?

Rashid Khan
 
D

Dave Peterson

I thought ID was the range that had the data validation cell--the one that you
change via the dropdown.

I guess I don't understand why you can't use a sheet level name (ID) for each
sheet.
 
P

prkhan56

Hi Dave,
Thanks a million...I have ID as sheet level name and it is working but
a slight problem is occurring. May be you would be kind enough to
rectify that too...

Is it possible that I can type the code together with number for
example EFG123 in the start box and EFG145... which would print the
record from 123 to 145 for company EFG. As my numbers repeat also in
some cases like ABC123 to ABC145...

Now the macro prints the same number from all the companies... for eg

When I put 123 in the start box and 145 in the end box.. then it will
print ABC123 to ABC145, and then EFG123 to EFG145... and so on... I
hope you get what I am trying to say.

I have no words to express my thanks for all the time and help you have
extended to me during all these days.

Can the above problem be rectified?
I thing the following needs to be modified :

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

the code should allow me to input the number eg ABC123 in the beginning
box and ABC145 in the second input box... and then print accordingly

Thanks a million once again.

Rashid Khan
 
D

Dave Peterson

Always 3 characters at the beginning...

If yes, then I'd ask for that prefix in another inputbox.

You'll have to add this portion to your current code (I've lost track of what's
current).

dim myPfx as string
.....


mypfx = inputbox(prompt:="what's the prefix")
if trim(mypfx)="" then
exit sub
end if
mypfx = left(mypfx & space(3),3) 'pad it with trailing spaces if required.

Then the other part:

For Each mycell In myRng.Cells
If LCase(mycell.Value) Like LCase(mypf) & "*" Then
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 'one more end if to match up with that if

End Sub
 
P

prkhan56

Hi Dave,
This is the final version of your code which I have in my system and it
works fine with a slight hitch.

It takes about 45 seconds to display the first record...I have tested
it with a sheet where there are no repetitions of numbers also...but
still it takes about 35+ seconds to display the record.
Can you guess what is making it to run so slow...May be you can give up
a booster...


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
Dim myPfx As String

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
myPfx = InputBox(prompt:="what's the prefix")
If Trim(myPfx) = "" Then
Exit Sub
End If
myPfx = Left(myPfx & Space(3), 3) 'pad it with trailing spaces if
required.
For Each myCell In myRng.Cells
If LCase(myCell.Value) Like LCase(myPfx) & "*" Then
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
End If
Next myCell
End Sub

Thanks for your help and all the support

You were really helpful and caring.

Rashid Khan
 
D

Dave Peterson

I don't see anything that pops up that would cause that kind of delay in your
code.

Do you have any event procedures in your workbook or worksheet?

If you do, maybe adding

application.enableevents = false
'right before this line
For Each myCell In myRng.Cells
'more code
Next myCell
'then
application.enableevents = true
end sub

A second guess. That application.calculate line may not be necessary. If you
have excel set for automatic calculation, you could try commenting that line.
 
P

prkhan56

Hi Dave,
You are really wonderful...I just commented the Automatic Calculation
line. Now I have it working.

Thanks a bunch once again.

Rashid Khan
 
D

Dave Peterson

Glad you got it working!
Hi Dave,
You are really wonderful...I just commented the Automatic Calculation
line. Now I have it working.

Thanks a bunch once again.

Rashid Khan
Click to expand...
 
K

keepITcool

after 3 weeks it is about time :)

(i participated early in the thread, so I keep getting new message in
thread alerts..)

I admire your tenacity.



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


Dave Peterson wrote :
 

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