Auto numbering when printing based on cell value

[starfox142]

Hi there,

Attached is a pallet label example our warehouse are using and they would like to know if there is a way to auto fill the pallet number based on the total pallet value.

EG

Total number of pallets is 10 so they would manually fill this in on the label (yellow cell on the right) and when they go to print it would print 1 of 10, 2 of 10, 3 of 10 etc

 

[KOKOSEK]

no tested, just write down:

Sub printpalletheaders()
For i = 1 To Sheet("PalletHeader").Range("B1").Value                      ' B1 as example, that address of RIGHT yellow cell
    Sheet("PalletHeader").Range("A1").Value = i     ' A1 as example, that address of LEFT yellow cell
    Sheet("PalletHeader").PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False, preview:=False
Next i
End Sub

 

[starfox142]

Thank you for getting back to me, I got the attached error.

Pallet label1.xlsx
	A	B	C	D	E	F	G	H	I
1	Company
2
3
4
5	Customer				Customer
6
7
8	Destination				Middlesbrough
9
10
11	Delivery Day				Saturday
12
13
14	Delivery Date				30/07/2021
15
16
17	Ship Via				Transport company
18
19
20	Window				Temp
21
22
23	Products				7		Qty	7
24
25
26	Pallet No						of	7
27
28
29	SSCC Code
30
31
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
E11:I13	List	=Sheet2!$B:$B
E5:I7	List	=Sheet2!$A:$A
E17:I19	List	=Sheet2!$C:$C

 

[KOKOSEK]

Sheets not sheet. As I said I've written down straight into browser and missed s 3 times.

Sub printpalletheaders()
For i = 1 To Sheets("PalletHeader").Range("H26").Value
    Sheets("PalletHeader").Range("E26").Value = i
    Sheets("PalletHeader").PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False, preview:=False
Next i
End Sub