Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Mr. Peterson's Code.. Print serially from a Sheet
ok would try and get back to you.
Thanks Rashid Khan |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Active cell counting in particular print page (one sheet having different print area) | Excel Worksheet Functions | |||
Why do I get a print error light trying to print an excel sheet ? | Excel Discussion (Misc queries) | |||
Need Code To Print From Code Modules | Excel Programming | |||
how to use Excel to generate alphabet serially | Excel Worksheet Functions | |||
Help with Mr. Dave Peterson's Code for Consolidating Many Sheets to One | Excel Programming |