Serial Number printing

[dave3944]

I need to know how to print a set of Work Instructions, each with a new Serial number taken from a list on another Worksheet.

I open a Workbook called "MO Retrieval" which contains various pieces of information, including the starting and ending Serial Numbers of the Part #'s. There may be one or more of each Part #, each having its own, unique Serial #. (Example: Part #A, Serial # 1; Part #B, Serial # 1; Part #B, Serial # 2; Part #B, Serial #3; Part #C, Serial #1; etc.) Then, I open the Workboook of each Part #.

Various cells in each Part # Workbook (Worksheet "Seat Traveler") retrieve data from specific cells on "MO Retrieval". One cell in the Part # Workbook is the "Serial #" cell. It displays the first Serial # in the series retrieved from "MO Retrieval" for the particular Part #. The beginning Serial # is in one column on "MO Retrieval", while the ending Serial # is one column to the right.

It looks like this (sorry, I don't know how to make it look like columns):

Part # | Qty | Order # | Beg. SN | End SN
1 | 10 | MO10000 | 12 | 21 (or, (Beg. Sn-1)+Qty)
2 | 4 | MO10001 | 256 | 259
3 | 1 | MO10002 | 47 |
4 | 1 | MO10003 | 15 |
5 | 22 | MO10004 | 74 | 95

Each Part # Workbook contains three Worksheets. The first (called DWG Rev) contains the latest blueprint revision data, the second (called Seat Traveler) contains the actual Work Instructions, and the third (called Squawk Sheet) is a sheet for Inspection to record any defects.

When it is time to print, three scenarios exist. The first (Part # 3 or 4, one Serial #) is no problem, I have a button (with a Macro assigned) which prints all sheets to the designated printer. No problem.

The second is a little more tricky. Right now, I manually change the Serial # and press a button (with a Macro assigned) that prints to the designated printer. If I get distracted, I may skip a Serial # or print the same Serial # twice. both cause problems on the Shop Floor. I need to automatically print all Worksheets, but with each successive copy, change the Serial # to the next highest number until it reaches the ending Serial # from "MO Retrieval".

The third scenario is the most complex. Right now I print all Worksheets on the first copy (beginning Serial #) with a button (with a Macro assigned), then manually change the Serial # and print only "Seat Traveler" and "Squawk Sheet" by using a second button. I need to automatically print all Worksheets on the first copy only. On each successive copy I only need to print "Seat Traveler" and "Squawk Sheet", changing the Serial # to the next highest number until it reaches the ending Serial # from "MO Retrieval".

I know this sounds incredibly complex, but I have to do it myself right now. I want to transfer the duty to Shop Floor Personnel. I would like to automate it, eliminating any possible difficulties or mistakes.

 

[Corni]

In order to automate the printing of the sheets from Part# Workbooks, it is important to set up a naming convention for the workbooks, in order to reflect the part # and the series index, for eg. if we consider part #1, with an order of 10, and series between 12 and 21, the first part could be contained in a workbook named Part1-12, the second in Part1-13 and so on up to Part1-21. This way you could command the program to open succesively all workbooks and print the sheets according to your needs. So as a first step please let me know how you named the workbooks, to make sure that the naming is compatible with the automation.

 

[dave3944]

Fair enough. Let me be more specific. My company makes aircraft seats for many of the world's largest airlines. Each airline buys their seats in different configurations, different styles which require a different Seat part #. An airline may have a contract with us to purchase, say 10 sets (called a Shipset) of seats to be installed in 10 aircraft. The FAA is very strict on the tracibility of anything that goes into an airplane, whether seats, avionics or anything else. Hence the need for a unique Serial #. Each is assigned a separate, unique Serial #.

Each Shipset is delivered on a different date, coinciding with an aircraft delivery date from Boeing or Airbus. Each Shipset is built and billed individually with a different Customer Order # (CO#). Therefore, I have a Folder labeled by Customer name, aircraft type (different customers purchase seats for different aircraft types), class (Tourist, First, or Business class), the Shipset #, and the CO#. (Example: AAL 737-800 TC #4 - CO#145678)

Within each folder, is a list of the specific seat #'s arranged in the order we plan to manufacture them (which coincides with the way they will be placed in the aircraft). (Example: 01 - 857524-401, 02 - 857524-402, etc.). Each Part # is a separate Workbook. Due to the differences in aircraft, customer preferences and class, there may be anywhere from 2 to 30 (or more) Part #'s (Workbooks) in a Shipset. But, as I stated in my previous post, we may make 10 Each of Part # 01 - 857524-401, 1 of Part # 02 - 857524-402, 14 of Part # 03 - 857524-403. The Work Instructions for each Part # are identical. The only difference is that, being Shipset #4, we have previously made 30 of Part # 01 - 857524-401 (Shipsets 1, 2 & 3), therefore the first Serial # for Shipset #4, Part # 01 - 857524-401 will be 31 and 40 for the 10th Part # 01 - 857524-401. The next Shipset (#5), Part # 01 - 857524-401 will begin with Serial # 41, and so on. Someone else is the "Keeper of the Official Serial # sequence for each Part #, I just use their data.

Within each folder is also the Workbook "MO Retrieval", which contains the Manufacturing Order # (MO#) for each Part # in Shipset #4, the manufacturing start date, the Shipset #, the CO# and the first & last serial #'s for this Shipset. Each Part # (Workbook) refers to specific cells in this Workbook to retrieve repetitive information. (They are all manufactured on the same date, they are all in the same Shipset, and have the same CO#). This keeps me from having to enter this data on 30 Workbooks. Whenever we run Shipset #5, I just copy the folder from Shipset #4, change the dates, Shipset #, CO# on MO Retrieval, run a database query to retrieve the new MO#'s and enter new range of Serial #'s. this part works pretty well.

Each Part # Workbook contains 3 Worksheets. "DWG Rev", which retrieves (via another Database query) the current Revision levels of all the Engineering Drawings required to assemble the seats. This is an FAA requirement. The next Worksheet contains the actual Work instructions to assemble all the components of this particular Part #. Again, per FAA requirements, this Worksheet (which is 4-7 printed pages long), must contain the Part #, Shipset #, CO#, Date of manufacture, MO# and Serial #.
the third sheet is a Squawk sheet which must be printed which documents any problems found by QA and any remedies which were used to correct them and who did them.

Each seat that goes down the line must have an accompanying unique set of Work Instructions. The FAA requires that each worker who assembles the various components to the seat apply their own unique "stamp" to this set of Work Instructions. We have to store the completed sets for up to 7 years, in case of an accident, so they can trace the seat back to us.

My first post describes the printing process. I was wondering if there was a way in Excel that was similar to the Mail Merge function in Word, which would insert (in the appropriate box on the second Worksheet) the first Serial # from MO Retrieval, print the required sheets, go back to MO Retrieval, fill in the next Serial # (if more than 1 seat is required), print the required sheets, etc. until the last Serial # for that Part # was reached.

Being that there are more Tourist class seats on any aircraft, it is not necessary to print the Worksheet DWG Rev for each Serial #, just on the first Serial #. However, since there are fewer First/Business class seats and they take longer to build, a DWG Rev Worksheet is printed with each Serial #. So there are 3 different printing scenarios: TC - 1st Serial # (All Worksheets); TC - succeeding Serial #'s (only Worksheet 2 & 3); and FC/BC - (All Worksheets, every Serial #).

It doesn't seem like it should be that hard, I'm just not smart enough to figure it out.

 

[Corni]

Based on your story I feel that I understood your requirements and working conditions. The assumptions I made in order to implement the code below are:
1) Sheet 'Seat Traveler' has the part # in cell A1 and serial number in cell A2.
2) Workbook 'MO Retrieval' is opened and has data stored in sheet #1, having Qty in col 2, Beg SN in col 4 and End SN in col 5.
If you have different locations from the above, you have to adjust the code accordingly.

Now here is the code you have to attach as a macro to a button in the respective part # workbook:

Dim intBegSN As Integer, intEndSN As Integer, intQty As Integer, i As Integer

'Initialize intBegSN, intEndSN and intQty by looking into MO Retrieval sheet
intBegSN = WorksheetFunction.VLookup(Sheets("Seat Traveler").Cells(1, 1), Workbooks("MO Retrieval.xls"). _
Sheets(1).Range("A1:E1000"), 4, False)
intEndSN = WorksheetFunction.VLookup(Sheets("Seat Traveler").Cells(1, 1), Workbooks("MO Retrieval.xls"). _
Sheets(1).Range("A1:E1000"), 5, False)
intQty = WorksheetFunction.VLookup(Sheets("Seat Traveler").Cells(1, 1), Workbooks("MO Retrieval.xls"). _
Sheets(1).Range("A1:E1000"), 2, False)
'Use an If/Else statement to handle differently TC from FC/BC seats
If InStr(ActiveWorkbook.FullName, "TC") > 0 Then
'Fill cell A2 in Seat Traveler with the initial serial number
Sheets("Seat Traveler").Cells(2, 1) = intBegSN
'Print all three sheets for the first serial number seat
Sheets(Array("DWG Rev", "Seat Traveler", "Squawk")).Select
Sheets("DWG Rev").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'If more than one seat print only two sheets for all remaining serial numbers seats
If intQty > 1 Then
For i = intBegSN + 1 To intEndSN
'Fill cell A2 with the serial number
Sheets("Seat Traveler").Cells(2, 1) = i
'Print set of two sheets for each serial number
Sheets(Array("Seat Traveler", "Squawk")).Select
Sheets("Seat Traveler").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next i
End If
Else
'Now handle FC/BC seats
'Fill cell A2 with the initial serial number
Sheets("Seat Traveler").Cells(2, 1) = intBegSN
'Print all three sheets for the first serial number seat
Sheets(Array("DWG Rev", "Seat Traveler", "Squawk")).Select
Sheets("DWG Rev").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'If more than one seat print all three sheets for all remaining serial numbers seats
If intQty > 1 Then
For i = intBegSN + 1 To intEndSN
'Fill cell A2 with the serial number
Sheets("Seat Traveler").Cells(2, 1) = i
'Print set of three sheets for each serial number
Sheets(Array("DWG Rev", "Seat Traveler", "Squawk")).Select
Sheets("DWG Rev").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next i
End If
End If

Give it a try and let me know how is working. Good luck.

 

[dave3944]

Thanks a million for the help. After I changed a couple of things, like the cell references you recommended, it ran perfectly. My co-workers think I'm an Excel GOD!!! I decided to just create 2 different macros (one for TC and one for FC/BC), since I don't make that distinction in the file name as your macro assumed. A typical file name would look like 01 - 857524-401.xls. There is no TC of FC/BC in the file name. That is no problem since I never print them at the same time anyway. Both macros work great. I did run into another situation with another airline contract. We bought out a competitor and now make their seat designs in-house. The FAA required that when we did that, all subsequent Seat Serial #'s begin with 'W' and then the Serial #, so that they would know which seats were made at our facility and which were made at the old competitor's facility. That makes some of your script inoperable. I think it has something to do with your declaration that the Beginning & Ending Serial #'s are numbers (Integers). With the addition of the 'W' at the beginning, they are now text. I'm not well versed enough to know what to change.

For example, if the Beginning Serial # is W 117 and the Ending Serial # is W 120, the Macro would need to print all sheets for Serial # W 117, change the Serial # to W 118 and print only "Seat Traveler" and "Squawk", etc., similar to the TC option in your macro. What do I change to get it to work right? I will create a third iteration of the macro just for this type seat.

Thanks for your time and willingness to share your talent with a newbie.

 

[Corni]

To address the new situation you have to declare variables for SN as strings instead of integers and the code will change to:

Dim strBegSN As String, strEndSN As String, intQty As Integer, i As Integer

'Initialize intBegSN, intEndSN and intQty by looking into MO Retrieval sheet
strBegSN = WorksheetFunction.VLookup(Sheets("Seat Traveler").Cells(1, 1), Workbooks("MO Retrieval.xls"). _
Sheets(1).Range("A1:E1000"), 4, False)
strEndSN = WorksheetFunction.VLookup(Sheets("Seat Traveler").Cells(1, 1), Workbooks("MO Retrieval.xls"). _
Sheets(1).Range("A1:E1000"), 5, False)
intQty = WorksheetFunction.VLookup(Sheets("Seat Traveler").Cells(1, 1), Workbooks("MO Retrieval.xls"). _
Sheets(1).Range("A1:E1000"), 2, False)
'Use an If/Else statement to handle differently TC from FC/BC seats
If InStr(ActiveWorkbook.FullName, "TC") > 0 Then
'Fill cell A2 in Seat Traveler with the initial serial number
Sheets("Seat Traveler").Cells(2, 1) = strBegSN
'Print all three sheets for the first serial number seat
Sheets(Array("DWG Rev", "Seat Traveler", "Squawk")).Select
Sheets("DWG Rev").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'If more than one seat print only two sheets for all remaining serial numbers seats
If intQty > 1 Then
If Left(strBegSN, 1) <> "W" Then
For i = CInt(strBegSN) + 1 To CInt(strEndSN)
'Fill cell A2 with the serial number
Sheets("Seat Traveler").Cells(2, 1) = i
'Print set of two sheets for each serial number
Sheets(Array("Seat Traveler", "Squawk")).Select
Sheets("Seat Traveler").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next i
Else
For i = CInt(Right(strBegSN, Len(strBegSN) - 2)) + 1 To CInt(Right(strEndSN, Len(strEndSN) - 2))
'Fill cell A2 with the serial number
Sheets("Seat Traveler").Cells(2, 1) = "W " & i
'Print set of two sheets for each serial number
Sheets(Array("Seat Traveler", "Squawk")).Select
Sheets("Seat Traveler").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next i
End If
End If
Else
'Now handle FC/BC seats
'Fill cell A2 with the initial serial number
Sheets("Seat Traveler").Cells(2, 1) = intBegSN
'Print all three sheets for the first serial number seat
Sheets(Array("DWG Rev", "Seat Traveler", "Squawk")).Select
Sheets("DWG Rev").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'If more than one seat print all three sheets for all remaining serial numbers seats
If intQty > 1 Then
If Left(strBegSN, 1) <> "W" Then
For i = CInt(strBegSN) + 1 To CInt(strEndSN)
'Fill cell A2 with the serial number
Sheets("Seat Traveler").Cells(2, 1) = i
'Print set of three sheets for each serial number
Sheets(Array("DWG Rev", "Seat Traveler", "Squawk")).Select
Sheets("DWG Rev").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next i
Else
For i = CInt(Right(strBegSN, Len(strBegSN) - 2)) + 1 To CInt(Right(strEndSN, Len(strEndSN) - 2))
'Fill cell A2 with the serial number
Sheets("Seat Traveler").Cells(2, 1) = "W " & i
'Print set of three sheets for each serial number
Sheets(Array("DWG Rev", "Seat Traveler", "Squawk")).Select
Sheets("DWG Rev").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next i
End If
End If
End If

I assumed based on your posting that serial numbers starting with W have a space immediately after W, i.e. between W and the numeric part of the string. You have to make the same changes to the new code as the ones you made on my initial code.

 

[dave3944]

Serial Number Printing

I have been using the new macro for a month now with no problems. I appreciate your input. i was in over my head. Thanks again