VBA for Generating Cycle Counts

[bneighbors9]

I am looking for a VBA assigned to a button that will generate a cycle count for inventory control purposes. Each time I press the button, a new list will populate and not repeat any part numbers from a previous time of pressing the button. The list of part numbers with description/ location are on sheet 1 , Column B through D. The count sheet that I need this information to go to is on sheet2.

The button needs to do the following
Put todays date in the top of the sheet2
Randomly pull 15 part numbers from sheet 1 and paste that onto the count sheet (Sheet2 in A4 through A18). It needs to not select any part number that has a date in the E column.
Once a part number has been selected, put todays date in column E on sheet 1 to show the date it was selected and to stop it from being selected again.
Be able to click the button over and over again to create a new list until all part numbers have been selected 1 time.

 

[kvsrinivasamurthy]

When you are selecting from the remaining data in sheet1, where you want to place the selected data. Below the existing data in Sheet2 or in some other sheet?

 

[bneighbors9]

When I press the button on sheet2, I would like for it to pull the first 15 available material numbers from sheet1 that do not have a date in the E column and place them into Sheet2. Right now it is pulling the material numbers at random. I would like for them to be in order from top of the list to the bottom. Each time I click the button, the next 15 material numbers with generate on sheet2 until there is not anymore available without a date in the E column.

 

[bneighbors9]

CC test page.xlsm
	A	B	C	D	E
1	STORAGE TYPE	MATERIAL	DESCRIPTION	STORAGE BIN	COMPLETE
2	FSP	0157221225	SEAT	HJ12FSP01A
3	FSP	08036D1630	CLIP	HJ12FSP01B
4	FSP	2A54611C50KC	PLATE ASSY CLAMP	HJ12FSP01C
5	FSP	2A670K1191	BRACKET	HJ12FSP01C
6	FSP	2079707420	BRACKET	HJ12FSP02A
7	FSP	2079707430	BRACKET	HJ12FSP02A
8	FSP	207943A130	BRACKET	HJ12FSP02B
9	FSP	2069437A90	small strip	HJ12FSP02C
10	FSP	20Y9703572	BRACKET	HJ12FSP03A
11	FSP	2089707180	BRACKET ARM	HJ12FSP03B
12	FSP	2089707190	BRACKET ARM	HJ12FSP03B
13	FSP	20Y7042E51	PLATE	HJ12FSP03B
14	FSP	2067072B60	PIPE	HJ12FSP03C
15	FSP	20Y7022H50	Grease tube	HJ12FSP03C
16	FSP	2A76215610	BRACKET	HJ12FSP04A
17	FSP	20Y6245172	CLAMP BRACKET	HJ12FSP04B
18	FSP	2077062B52KS	Grease Tube	HJ12FSP04C
19	FSP	2087066B40KS	PIPE SOZAI	HJ12FSP04C
20	FSP	2089446B40KS	PLATE SOZAI	HJ12FSP04C
21	FSP	2056221150	BRACKET	HJ12FSP05A
22	FSP	2A56228280	BRACKET	HJ12FSP05B
23	FSP	2A66221240	BRACKET	HJ12FSP05C
24	FSP	2A67021140	BRACKET	HJ12FSP06A
25	FSP	2A57011130	PLATE	HJ12FSP06B
26	FSP	0158003024	NUT	HJ12FSP06C
27	FSP	2086274210	SEAT	HJ12FSP06C
28	FSP	2A77011150	BRACKET	HJ12FSP07A
29	FSP	2A66221250	BRACKET	HJ12FSP07B
30	FSP	2A67021220	BRACKET	HJ12FSP07C
31	FSP	2A56228260	BRACKET BOOM TUBE SADDLE	HJ12FSP08A
32	FSP	2056221140	BRACKET	HJ12FSP08B
33	FSP	2A66211290	BRACKET	HJ12FSP08C
34	FSP	2086224440	BRACKET	HJ12FSP09A
35	FSP	2A66211280	BRACKET	HJ12FSP09B
36	FSP	2076242830	TUBE CLAMP BRACKET	HJ12FSP09C
37	FSP	S205270022	BAR-2(SUTEZAI) 9mm sq X 300mm lg	HJ12FSP10A
38	FSP	2086274241	CLAMP BRACKET	HJ12FSP10B
39	FSP	S205270032	1/2'' BAR 250MM LG	HJ12FSP10C
40	FSP	2086227510	BRACKET PC490LCI-11	HJ12FSP11A
41	FSP	2086274251	CLAMP BRACKET	HJ12FSP11B
42	FSP	2A56228270	BRACKET BOOM TUBE SADDLE	HJ12FSP11C
43	FSP	2A546A2M90JA	PLATE	HJ12FSP12A
44	FSP	2076274281	BRACKET	HJ12FSP12B
45	FSP	22U46A8A90	PLATE	HJ12FSP12C
46	FSP	2A56226450	BRACKET	HJ12FSP13A
47	FSP	2A77011140	BRACKET	HJ12FSP13B
48	FSP	08036D2045	CLIP	HJ12FSP13C
49	FSP	2A56226460	BRACKET	HJ12FSP14A
50	FSP	2A67021320	SEAT	HJ12FSP14B
51	FSP	5678912140	SEAT	HJ12FSP14B
52	FSP	2A54611E41KB	PLATE ***'Y	HJ12FSP14C
53	FSP	2A546A2420	SEAT, PLATE A.	HJ12FSP15A
54	FSP	0101163030	BOLT	HJ12FSP15B
55	FSP	2A546A2430	SEAT, PLATE A.	HJ12FSP15C
56	FSP	2074671D20KS	long seat (6 holes)	HJ12FSP16A
57	FSP	2074661E20KA	PLATE ***'Y	HJ12FSP16B
58	FSP	2A74611910	SEAT	HJ12FSP16C
59	FSP	22U4621A90KS	PLATE SOZAI	HJ12FSP17A
60	FSP	2A646A1A31JA	PLATE ASEMBLY	HJ12FSP17B
61	FSP	2A64631B51	BRACKET	HJ12FSP17C
62	FSP	2074672C51JA	ANGLE A.	HJ12FSP18A
63	FSP	2A54611C41KC	PLATE ASSY CLAMP	HJ12FSP18B
64	FSP	2A54611C41KD	PLATE ASSY CLAMP	HJ12FSP18B
65	FSP	2084641C10	BRACKET	HJ12FSP18C
66	FSP	2074661E20KD	PLATE ***'Y	HJ12FSP19A
67	FSP	2084621C60KA	PLATE ***'Y	HJ12FSP19B
68	FSP	2A54626A30	BRACKET	HJ12FSP19B
69	FSP	2087027A30	SEAT	HJ12FSP19C
70	FSP	2A54611C60	PLATE	HJ12FSP19C
71	FSP	2097215210	SEAT	HJ12FSP20A
72	FSP	0157221019	SEAT	HJ12FSP20B
73	FSP	21T4615140	SEAT, M10X1.50 (X2) L60.00	HJ12FSP20B
74	FSP	2334611850	SEAT	HJ12FSP20B
75	FSP	2074661E20KE	PLATE ***'Y	HJ12FSP20C
76	FSP	5661611240	BOSS	HJ12FSP20C
77	FSP	2014673E61KA	PLATE ASSEMBLY	HJ12FSP21A
78	FSP	2A54611C51KA	PLATE ASSY CLAMP	HJ12FSP21A
79	FSP	2A54611F90KC	PLATE	HJ12FSP21B
80	FSP	2A64611B40	BRACKET	HJ12FSP21B
81	FSP	2A54611F90KD	PLATE	HJ12FSP21C
82	FSP	20Y3082B60	TR FRAME (NRW)-210LL/230F	HJ12FSP22A
83	FSP	20Y3082B70	TR FRAME (NARROW) - PC210LL	HJ12FSP22A
84	FSP	2083012160	PLATE	HJ12FSP22C
85	FSP	2A76215150	BRACKET	HJ12FSP22C
86	FSP	2083012130	BOLT,TRACK FRAME	HJ12FSP23A
87	FSP	2083031650	COLLAR	HJ12FSP23A
88	FSP	2064621161	BRACKET	HJ12FSP23B
89	FSP	2083065C30KS	BAR SOZAI	HJ12FSP23B
90	FSP	20Y4651361	BRACKET	HJ12FSP23B
91	FSP	0157221232	SEAT	HJ12FSP23C
92	FSP	2A56226480	BRACKET	HJ12FSP23C
93	FSP	2A646A1A20	SEAT	HJ12FSP23C
94	FSP	2084671D80	PLATE PC 360	HJ12FSP24B
95	FSP	2A67021330	SEAT	HJ12FSP24B
96	FSP	0157221422	SEAT	HJ12FSP28A
97	FSP	0157221219	SEAT	HJ12FSP28B
98	FSP	2856219360	BOSS, M12X1.75 L35.00	HJ12FSP28B
99	FSP	2059706371	SEAT	HJ12FSP28C
100	FSP	2A77011130	BRACKET	HJ12FSP29
FSP

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E964	Expression	=$E2<>""	text	NO

 

[bneighbors9]

CC test page.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	D74 CYCLE COUNT DATA SHEET
2
3	PART NUMBER	DISCRIPTION	LOCATION	COUNT	2ND COUNT	FINAL	BOOK	VAR
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19		______________________________ 2ND CNT SIGNATURE:_________________________________ ID NUMBER:________________
20	1ST CNT SIGNATURE:
21	DATE:
COUNT SHEET

 

[kvsrinivasamurthy]

When I press the button on sheet2, I would like for it to pull the first 15 available material numbers from sheet1 that do not have a date in the E column and place them into Sheet2. Right now it is pulling the material numbers at random. I would like for them to be in order from top of the list to the bottom. Each time I click the button, the next 15 material numbers with generate on sheet2 until there is not anymore available without a date in the E column.

In post#1 you asked for random selection. When you are selecting 2nd and further times where to place selected data in sheet2. Every time replace a4:c18 data by new data or some other range.?

 

[bneighbors9]

Yes I would like to change what was said in the first post from random selection to selecting in order. each time it should replace A4:C18

 

[kvsrinivasamurthy]

Try.

Sub SelectPartNumbers()
Dim T&, Lr&, cnt&
Dim A, B, Dt, M
Lr = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
A = Sheets("Sheet1").Range("B2:D" & Lr)
Dt = Sheets("Sheet1").Range("E2:E" & Lr)
cnt = WorksheetFunction.Min(WorksheetFunction.CountIf(Sheets("Sheet1").Range("E2:E" & Lr), ""), 15)
M = Filter(Evaluate("transpose(If('Sheet1'!e2:e" & Lr & "="""",'Sheet1'!b2:b" & Lr & ",False))"), False, False)
If cnt > 0 Then
ReDim B(0 To cnt - 1, 1 To 3)
For T = 0 To cnt - 1
B(T, 1) = A(M(T), 1): B(T, 2) = A(M(T), 2): B(T, 3) = A(M(T), 3): Dt(M(T), 1) = Date
Next T
Sheets("Sheet1").Range("E2:E" & Lr) = Dt
Sheets("Sheet1").Range("E2:E" & Lr).NumberFormat = "dd-mm-yyyy"
With Sheets("Sheet2")
.Range("A4:C18").Clear
.Range("A4:C" & 4 + cnt - 1) = B
.Range("B1") = Date
End With
Else
Sheets("Sheet2").Range("A4:C18").Clear
End If

MsgBox cnt & " Part numbers are selected", vbOKOnly, "Free Part Numbers"
End Sub

 

[bneighbors9]

The debugger is highlighting this

 

[kvsrinivasamurthy]

What is the error displayed.

 

[bneighbors9]