Create a list from certain cells

[Trueblue862]

Hi, this is only my second thread, thankyou for the great help I received in the first one.

I'm helping a friend set up a home delivery fruit and veg business and I'm up to my final stage in this project, I need to generate a picking list from my invoice. When I finalise the invoice I need it to copy across the products and quantity of each along with the delivery address and invoice number to aid with packing products into the correct box. Then if it's not too difficult I need to sort by product. I'm well out of my depth on this project, but have welcomed the challenge it has presented. It has been a very steep learning curve, but I can't help but feel I really need to do a course on VBA to do it effectivly.

This is the code I have at the moment, I've adapted it from something I found online.
I'm not fluent enough in coding to see where it's wrong.
Any help would be greatly appreciated.

Sub PackingList()
Dim InvoiceNumber As Integer
Dim Description As String
Dim Address As String
Dim Quantity As Integer
Dim cVal As String, rVal As String
Dim fCol As Range, fRow As Range, cl As Range
Dim Ws As Worksheet
      Set Ws = Worksheets("Invoice").Select
         InvoiceNumber = Ws.Range("E4")
            Description = Ws.Range("C14:C33")
               Quantity = Ws.Range("B14:B33")
                  Address = Ws.Range("B8")
            Worksheets("Packing List").Select
         Worksheets("Packing List").Range("A1").Select
     If Worksheets("Packing List").Range("A1").Offset(1, 0) <> "" Then
    Worksheets("Packing List").Range("A1").End(x1Down).Select
End If
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = InvoiceNumber
End Sub

This is my invoice

Ordering Sheet ver 2.xlsm
	A	B	C	D	E	F	G	H	I	J
1		Ward's Produce		INVOICE
2		somebody@outlook.com		ABN:	99 999 999 999
3		Sometown, Qld		DATE:	May 31, 2020
4		4650		INVOICE #	163
5		Phone: 1111 111 111
6		Bill To:
7			Delivery Details		0
8
9
10
11		0000000000
12
13		Quantity	DESCRIPTION	Unit Price	AMOUNT
14					$ -
15					$ -
16					$ -
17					$ -
18					$ -
19					$ -
20					$ -
21					$ -
22					$ -
23					$ -
24					$ -
25					$ -
26					$ -
27					$ -
28					$ -
29					$ -
30					$ -
31					$ -
32					$ -
33					$ -
34				SUBTOTAL	$ -
35				DELIVERY
36				TOTAL	$ -
37
38		THANK YOU FOR YOUR BUSINESS!
39
40
41
42
Invoice

Cell Formulas
Range	Formula
E3	E3	=TODAY()
E7	E7	=IFERROR(XLOOKUP(B7,Table2[[#All],[Customer Name]],Table2[[#All],[Delivery Day]]),"")
B8	B8	=IFERROR(XLOOKUP(B7,Table2[[#All],[Customer Name]],Table2[[#All],[Address]]),"")
B9	B9	=IFERROR(XLOOKUP(B7,Table2[[#All],[Customer Name]],Table2[[#All],[City]]),"")
B10	B10	=IFERROR(XLOOKUP(B7,Table2[[#All],[Customer Name]],Table2[[#All],[Postcode ]]),"")
B11	B11	=IFERROR(XLOOKUP(B7,Table2[[#All],[Customer Name]],Table2[[#All],[Phone Number]]),"")
D14:D17	D14	=IFERROR(XLOOKUP([@DESCRIPTION],Sheet1!C1:C98,Sheet1!B1:B98),"")
D18	D18	=IFERROR(XLOOKUP([@DESCRIPTION],Sheet1!C4:C101,Sheet1!B4:B101),"")
D19	D19	=IFERROR(XLOOKUP([@DESCRIPTION],Sheet1!C4:C101,Sheet1!B4:B101),"")
D20	D20	=IFERROR(XLOOKUP([@DESCRIPTION],Sheet1!C4:C101,Sheet1!B4:B101),"")
D21	D21	=IFERROR(XLOOKUP([@DESCRIPTION],Sheet1!C4:C101,Sheet1!B4:B101),"")
D22	D22	=IFERROR(XLOOKUP([@DESCRIPTION],Sheet1!C4:C101,Sheet1!B4:B101),"")
D23	D23	=IFERROR(XLOOKUP([@DESCRIPTION],Sheet1!C4:C101,Sheet1!B4:B101),"")
D24	D24	=IFERROR(XLOOKUP([@DESCRIPTION],Sheet1!C4:C101,Sheet1!B4:B101),"")
D25	D25	=IFERROR(XLOOKUP([@DESCRIPTION],Sheet1!C4:C101,Sheet1!B4:B101),"")
D26	D26	=IFERROR(XLOOKUP([@DESCRIPTION],Sheet1!C4:C101,Sheet1!B4:B101),"")
D27	D27	=IFERROR(XLOOKUP([@DESCRIPTION],Sheet1!C4:C101,Sheet1!B4:B101),"")
D28	D28	=IFERROR(XLOOKUP([@DESCRIPTION],Sheet1!C4:C101,Sheet1!B4:B101),"")
D29	D29	=IFERROR(XLOOKUP([@DESCRIPTION],Sheet1!C4:C101,Sheet1!B4:B101),"")
D30	D30	=IFERROR(XLOOKUP([@DESCRIPTION],Sheet1!C4:C101,Sheet1!B4:B101),"")
D31	D31	=IFERROR(XLOOKUP([@DESCRIPTION],Sheet1!C4:C101,Sheet1!B4:B101),"")
D32	D32	=IFERROR(XLOOKUP([@DESCRIPTION],Sheet1!C4:C101,Sheet1!B4:B101),"")
D33	D33	=IFERROR(XLOOKUP([@DESCRIPTION],Sheet1!C4:C101,Sheet1!B4:B101),"")
E14:E33	E14	=PRODUCT([@[Unit Price]],[@Quantity])
E34	E34	=SUBTOTAL(109,[AMOUNT])
E35	E35	=IFERROR(LOOKUP(Invoice[[#Totals],[AMOUNT]],{25;40},7),"")
E36	E36	=SUM(E34:E35)

Named Ranges
Name	Refers To	Cells
Sheet1!Print_Area	=Sheet1!$C:$C,Sheet1!$E:$E,Sheet1!$G:$G,Sheet1!$I:$I,Sheet1!$K:$N	D14

This is my packing list

Ordering Sheet ver 2.xlsm
	A	B	C	D	E
1	Description	Quantity	Unit	Invoice Number	Address
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
Packing List

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=Sheet1!$Y$1#

 

[ask2tsp]

Hi TrueBlue,
could you show the Table2 contents (just a few rows)?

 

[Trueblue862]

Ok, I'll try and explain it a little clearer, I need Column A on the second table to pull data from cells C14:C33 on the invoice.

Column B needs to get data from B14:B33.

Column C can be left out of the macro, I'll simply use a yes /no formula for that.

Column D needs to collect its data from cell E4

Column E needs to collect it's data from cell B8

I also need the whole table to sort alphabetically by column A.

I will insert the code to activate off the button which saves and prints the invoice.

Cells C14:C33 collect their data from a data validation drop down list.

I have managed to create many blunders while trying to create this code, I'm very much an amateur when it comes to coding, most iterations simply don't work, but my best version copied across the entire data validation list, not just the data.
I hope this makes it a bit clearer as to what I'm trying to achieve.

 

[ask2tsp]

Now you confuse me even more. Please just honor my previous request to publish a few rows of Table2. After that I think I can walk you through the steps to a solution.

 

[Trueblue862]

Ok, it’s getting quite late here tonight, I’ll sort it out for you tomorrow after I finish work.

 

[Trueblue862]

Hi Here is what I'm trying to acheive, I hope this helps.

Ordering Sheet ver 2 - Copy.xlsm
	A	B	C	D	E	F
1	Description	Quantity	Unit	Invoice Number	Address
2	700g eggs	2	Units	163	2 Abbey Rd
3	Apples Kg	1	Kg	163	2 Abbey Rd
4	Avocadoes	3	Units	164	1 Road St
5	baby spinach	2	Units	163	2 Abbey Rd
6	Bananas cav kg	4	Kg	165	3 Street Rd
7	Bananas lady kg	2	Kg	166	4 Park La
8	Bananas sugar kg	1	Kg	163	2 Abbey Rd
9	Beans kg	3	Kg	165	3 Street Rd
10	Beetroot kg	2	Kg	166	4 Park La
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Packing List

 

[ask2tsp]

i get the picture 80%.
Questions remaining:

1. The value in column C ' Unit' is that a column in the Products (Sheet1) table?
   (that's why I asked twice for a few rows of this table/sheet)
2. When do you add the Invoice to the Packing list?
   I assume using a command button that prints the invoice, adds invoice lines to the packing list and clear the invoice
3. How do you handle out of stock items? (maybe later)
4. Do you want to save the invoice for future reference?

 

[mikerickson]

I work in a warehouse and I think that the OP has the process backwards.
Usually the process is Invoice after Pick List.
The typical flow of paper is Customer Order then Pick/Pack List then the Inovice (reflecting actual (vs. supposed) inventory and items that couldn't be shipped.)
Generating an Invoice and a Pick List at the same time is showing an awful lot of confidence that the inventory database is correct.

 

[Trueblue862]

i get the picture 80%.
Questions remaining:

1. The value in column C ' Unit' is that a column in the Products (Sheet1) table?
   (that's why I asked twice for a few rows of this table/sheet)
2. When do you add the Invoice to the Packing list?
   I assume using a command button that prints the invoice, adds invoice lines to the packing list and clear the invoice
3. How do you handle out of stock items? (maybe later)
4. Do you want to save the invoice for future reference?

1. The value in column C is done using a simple yes/no function and doesn't need to be included in this.
2. There is a button which finalises and saves the invoice and this needs to be initialised from this.
3. Out of stock items aren't an issue, as everything is pre-order. Order by Wednesday, processed and ordered on Thursday and delivered on Friday through Sunday
4. The invoice already saves, I simply need to copy the data from the invoice to the packing list.

 

[Trueblue862]

I work in a warehouse and I think that the OP has the process backwards.
Usually the process is Invoice after Pick List.
The typical flow of paper is Customer Order then Pick/Pack List then the Inovice (reflecting actual (vs. supposed) inventory and items that couldn't be shipped.)
Generating an Invoice and a Pick List at the same time is showing an awful lot of confidence that the inventory database is correct.

A "normal" inventory system will not work for this business hence why I'm building this instead of using an already built system. All products are pre-ordered by the customer and then delivered direct from the farmer to the customer. The first customers get their produce within 24hrs of it being picked on the farm. This is a one man operation and it needs to be as simple and straight forward as possible. Additionally it has been colated by the end user to produce exactly what he needs, I'm just giving him a hand to get it all working.