Need help with Excel worksheet
 

I am trying to create a worksheet that will be a 'loading sheet' for my
warehouse operations. I need some help if possible.
What I have is an item number eg, #1, which may have 3 components which all
have to be loaded to ensure it's complete, item #2, may have 4 pieces etc.
What I would like would be for an excel sheet to generate a line for each
component part, in the case of #1, 3 lines all stating #1, 4 lines stating
#2 etc.
I need a cell next to each component to manually write what package # the
warehouse have labelled the piece when loading onto the truck.
If anyone could help, I'd be really grateful.
Thanks in advance.
Jamie

Need help with Excel worksheet
 

If I understand your scenario correctly, I think you need a couple things
first.
1. You need a Bill of Material which contains all the Items and their
components, preferably like this:
ColA ColB
Item1 Component1
Item1 Component2
Item1 Component3
Item2 Component1
Item2 Component2
Item3 Component3
Item4 Component4
etc.

2. Then you need a sheet where you enter the Items that are being loaded on
the truck.
3. Then I would suggest adding a menu item or a button on the sheet that
has code behind it that creates the packing slip for you. If one enters
Item1 on the sheet discussed in #2 above, then after clicking a button you'd
have a sheet like this:

Item Component Package #
Item1 Componen1 _______
Item1 Component2 _______
Item1 Component3 _______

Is that what you want this to do?

Need help with Excel worksheet
 

Mike,

Thanks for the reply.

Basically I have an Excel sheet which has the item number, it also has the
number of component parts that the item has. What I want is a formula which
checks the number of components for each item, then makes a load sheet
mentioning each component part, for example.

Item # Component parts
1 3
2 5
3 1

Item 1 component 1 of 3
Item 1 component 2 of 3
Item 1 component 3 of 3
Item 2 component 1 of 5
Item 2 component 2 of 5
etc
if possible.
If not, simply
Item 1 component 1
Item 1 component 2
Item 1 component 3

I hope this makes sense.

Thanks
Jamie

"Mike H." wrote:

If I understand your scenario correctly, I think you need a couple things
first.
1. You need a Bill of Material which contains all the Items and their
components, preferably like this:
ColA ColB
Item1 Component1
Item1 Component2
Item1 Component3
Item2 Component1
Item2 Component2
Item3 Component3
Item4 Component4
etc.

2. Then you need a sheet where you enter the Items that are being loaded on
the truck.
3. Then I would suggest adding a menu item or a button on the sheet that
has code behind it that creates the packing slip for you. If one enters
Item1 on the sheet discussed in #2 above, then after clicking a button you'd
have a sheet like this:

Item Component Package #
Item1 Componen1 _______
Item1 Component2 _______
Item1 Component3 _______

Is that what you want this to do?

Need help with Excel worksheet
 

In essence, I think you've got it

"Mike H." wrote:

If I understand your scenario correctly, I think you need a couple things
first.
1. You need a Bill of Material which contains all the Items and their
components, preferably like this:
ColA ColB
Item1 Component1
Item1 Component2
Item1 Component3
Item2 Component1
Item2 Component2
Item3 Component3
Item4 Component4
etc.

2. Then you need a sheet where you enter the Items that are being loaded on
the truck.
3. Then I would suggest adding a menu item or a button on the sheet that
has code behind it that creates the packing slip for you. If one enters
Item1 on the sheet discussed in #2 above, then after clicking a button you'd
have a sheet like this:

Item Component Package #
Item1 Componen1 _______
Item1 Component2 _______
Item1 Component3 _______

Is that what you want this to do?

Need help with Excel worksheet
 

This should work:
It assumes you have a sheet "components" which has your bills of material.
It creates a load sheet as a separate sheet. HTH


Sub CreateLoadSheet()
Dim DataArray(500, 2) As Variant
Dim ToShip(500, 2) As Variant
Dim ToShipCntr As Long
Dim Nbr As Long
Dim X As Long
Dim Y As Long

Let X = 2
'first we'll look through your list of items to ship, starting in row 2, col A
Do While True
If Cells(X, 1).Value = Empty Then Exit Do
ToShipCntr = ToShipCntr + 1
ToShip(ToShipCntr, 1) = Cells(X, 1).Value
X = X + 1
Loop
'now we know what is shipping. So go get components.
Sheets("Components").Select
X = 2
Do While True
If Cells(X, 1).Value = Empty Then Exit Do
For Y = 1 To ToShipCntr
If Cells(X, 1).Value = ToShip(Y, 1) Then
ToShip(Y, 2) = ToShip(Y, 2) + 1 'counter
Nbr = Nbr + 1
DataArray(Nbr, 1) = Y
DataArray(Nbr, 2) = Cells(X, 2).Value
End If
Next
X = X + 1
Loop

Dim MyEntries As String
Workbooks.Add Template:="Workbook"
MyEntries = ActiveWorkbook.Name
Cells(1, 1).Value = "Item"
Cells(1, 2).Value = "Component"
X = 1
For Y = 1 To ToShipCntr
For Z = 1 To Nbr
If DataArray(Z, 1) = Y Then
X = X + 1
Cells(X, 1).Value = ToShip(Y, 1)
Cells(X, 2).Value = DataArray(Z, 2) & " of " & ToShip(Y, 2)
End If
Next
Next

Range("C1").Select
ActiveCell.FormulaR1C1 = "Package #"
For Y = 1 To X - 1
Cells(Y + 1, 3).Select
ActiveCell.FormulaR1C1 = "________"
Next
Cells.Select
Cells.EntireColumn.AutoFit
Set PrtRng = Range(Cells(1, 1), Cells(X + 2, 3))
With ActiveSheet.PageSetup
.Zoom = False
.PrintArea = PrtRng.Address
.PrintTitleRows = "$1:$1"
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = 10
End With
Columns("A:A").Select
With Selection
.HorizontalAlignment = xlLeft
End With
Cells(2, 1).Select
ActiveWindow.FreezePanes = True
ActiveWindow.SelectedSheets.PrintPreview

End Sub