Create New Sheet with Specific Name Based on Variable Cell

[SBExcel123]

I have an excel file that has 3 main sheets.
The first sheet is a Table of Contents for different line items (Line Item List).
The second sheet is a Summary (0000_Summary). Each Line Item will have its own summary sheet.
The third sheet is a Form (0000-01). Each Line Item can have several forms as new forms are needed for each line item.

My goal would be to create a new sheet, named with the next available Source Number for the Line Item once a sheet has been completed for the current Source Number. For example, once I have completed a form for 0000-01, I would like to have some VBA button (on the Summary sheet) that allows me to copy 0000-01 (Form sheet), clear it, and rename it to 0000-02. I'd also like to create a hyperlink to 0000-02 on the summary sheet once it's been created. Once 0000-02 is complete, I'd like to copy it, clear it, name it 0000-03, and hyperlink it. And so on and so forth.

Test%20Project (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K
1	Line Item	Category	Item Number	Item Description	Units	Bid Quantity	To-Date Quantity	Remaining Quantity		Sheet Name	Reference Cell
2	0000	0100	100-00000	TEST ITEM 1	EACH	100	50	50		0000_Summary	O2
3	0005	0200	200-00000	TEST ITEM 2	EACH	200	#REF!	#REF!		0005_Summary	O2
4	0010	0300	300-00000	TEST ITEM 3	EACH	300	#REF!	#REF!		0010_Summary	O2
Line Item List

Cell Formulas
Range	Formula
G2:G4	G2	=INDIRECT("'"&A2&"_Summary'!"&K2)
H2:H4	H2	=F2-G2

Test%20Project (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1								0000_Summary		LINE ITEM	0000			BID QUANTITY	100
2										CATEGORY NUMBER	0100			TO-DATE QUANTITY	50
3										ITEM NUMBER	100-00000			REMAINING QUANTITY	50
4										DESCRIPTION	TEST ITEM 1
5										UNITS	EACH
6
7										SOURCE NUMBER	DATE COMPLETED	QUANTITY	ESTIMATE	STATUS
8	0000	0000-01	'0000-01'	0000	01	201-00000	0000 | 201-00000	TEST ITEM 1	EACH	0000-01	12/12/2012	50	1	CLOSED - USE NEXT AVAILABLE SOURCE NUMBER
9	0000	0000-02	'0000-02'	0000	02	201-00000	0000 | 201-00000	TEST ITEM 1	EACH	0000-02				OPEN - CREATE NEW FORM
10	0000	0000-03	'0000-03'	0000	03	201-00000	0000 | 201-00000	TEST ITEM 1	EACH
11	0000	0000-04	'0000-04'	0000	04	201-00000	0000 | 201-00000	TEST ITEM 1	EACH
12	0000	0000-05	'0000-05'	0000	05	201-00000	0000 | 201-00000	TEST ITEM 1	EACH
13	0000	0000-06	'0000-06'	0000	06	201-00000	0000 | 201-00000	TEST ITEM 1	EACH
14	0000	0000-07	'0000-07'	0000	07	201-00000	0000 | 201-00000	TEST ITEM 1	EACH
15	0000	0000-08	'0000-08'	0000	08	201-00000	0000 | 201-00000	TEST ITEM 1	EACH
16	0000	0000-09	'0000-09'	0000	09	201-00000	0000 | 201-00000	TEST ITEM 1	EACH
17	0000	0000-10	'0000-10'	0000	10	201-00000	0000 | 201-00000	TEST ITEM 1	EACH
18	0000	0000-11	'0000-11'	0000	11	201-00000	0000 | 201-00000	TEST ITEM 1	EACH
19	0000	0000-12	'0000-12'	0000	12	201-00000	0000 | 201-00000	TEST ITEM 1	EACH
20	0000	0000-13	'0000-13'	0000	13	201-00001	0000 | 201-00001	TEST ITEM 1	EACH
21	0000	0000-14	'0000-14'	0000	14	201-00002	0000 | 201-00002	TEST ITEM 1	EACH
22	0000	0000-15	'0000-15'	0000	15	201-00003	0000 | 201-00003	TEST ITEM 1	EACH
23	0000	0000-16	'0000-16'	0000	16	201-00004	0000 | 201-00004	TEST ITEM 1	EACH
24	0000	0000-17	'0000-17'	0000	17	201-00005	0000 | 201-00005	TEST ITEM 1	EACH
25	0000	0000-18	'0000-18'	0000	18	201-00006	0000 | 201-00006	TEST ITEM 1	EACH
26	0000	0000-19	'0000-19'	0000	19	201-00007	0000 | 201-00007	TEST ITEM 1	EACH
27	0000	0000-20	'0000-20'	0000	20	201-00008	0000 | 201-00008	TEST ITEM 1	EACH
28	0000	0000-21	'0000-21'	0000	21	201-00009	0000 | 201-00009	TEST ITEM 1	EACH
29	0000	0000-22	'0000-22'	0000	22	201-00010	0000 | 201-00010	TEST ITEM 1	EACH
30	0000	0000-23	'0000-23'	0000	23	201-00011	0000 | 201-00011	TEST ITEM 1	EACH
31	0000	0000-24	'0000-24'	0000	24	201-00012	0000 | 201-00012	TEST ITEM 1	EACH
32	0000	0000-25	'0000-25'	0000	25	201-00013	0000 | 201-00013	TEST ITEM 1	EACH
33	0000	0000-26	'0000-26'	0000	26	201-00014	0000 | 201-00014	TEST ITEM 1	EACH
34	0000	0000-27	'0000-27'	0000	27	201-00015	0000 | 201-00015	TEST ITEM 1	EACH
35	0000	0000-28	'0000-28'	0000	28	201-00016	0000 | 201-00016	TEST ITEM 1	EACH
36	0000	0000-29	'0000-29'	0000	29	201-00017	0000 | 201-00017	TEST ITEM 1	EACH
37	0000	0000-30	'0000-30'	0000	30	201-00018	0000 | 201-00018	TEST ITEM 1	EACH
38	0000	0000-31	'0000-31'	0000	31	201-00019	0000 | 201-00019	TEST ITEM 1	EACH
39	0000	0000-32	'0000-32'	0000	32	201-00020	0000 | 201-00020	TEST ITEM 1	EACH
40	0000	0000-33	'0000-33'	0000	33	201-00021	0000 | 201-00021	TEST ITEM 1	EACH
41	0000	0000-34	'0000-34'	0000	34	201-00022	0000 | 201-00022	TEST ITEM 1	EACH
42	0000	0000-35	'0000-35'	0000	35	201-00023	0000 | 201-00023	TEST ITEM 1	EACH
43	0000	0000-36	'0000-36'	0000	36	201-00024	0000 | 201-00024	TEST ITEM 1	EACH
44	0000	0000-37	'0000-37'	0000	37	201-00025	0000 | 201-00025	TEST ITEM 1	EACH
45	0000	0000-38	'0000-38'	0000	38	201-00026	0000 | 201-00026	TEST ITEM 1	EACH
46	0000	0000-39	'0000-39'	0000	39	201-00027	0000 | 201-00027	TEST ITEM 1	EACH
47	0000	0000-40	'0000-40'	0000	40	201-00028	0000 | 201-00028	TEST ITEM 1	EACH
48	0000	0000-41	'0000-41'	0000	41	201-00029	0000 | 201-00029	TEST ITEM 1	EACH
49	0000	0000-42	'0000-42'	0000	42	201-00030	0000 | 201-00030	TEST ITEM 1	EACH
50	0000	0000-43	'0000-43'	0000	43	201-00031	0000 | 201-00031	TEST ITEM 1	EACH
51	0000	0000-44	'0000-44'	0000	44	201-00032	0000 | 201-00032	TEST ITEM 1	EACH
52	0000	0000-45	'0000-45'	0000	45	201-00033	0000 | 201-00033	TEST ITEM 1	EACH
53	0000	0000-46	'0000-46'	0000	46	201-00034	0000 | 201-00034	TEST ITEM 1	EACH
54	0000	0000-47	'0000-47'	0000	47	201-00035	0000 | 201-00035	TEST ITEM 1	EACH
55	0000	0000-48	'0000-48'	0000	48	201-00036	0000 | 201-00036	TEST ITEM 1	EACH
0000_Summary

Cell Formulas
Range	Formula
H1	H1	=MID(CELL("filename",D1),FIND("]",CELL("filename",D1))+1,255)
O1	O1	=VLOOKUP(K1,'Line Item List'!A1:H4,6,FALSE)
O2	O2	=SUM(L8:L55)
O3	O3	=O1-O2
K1	K1	=LEFT(H1,4)
K2	K2	=VLOOKUP(K1,'Line Item List'!A1:H4,2,FALSE)
K3	K3	=VLOOKUP(K1,'Line Item List'!A1:H4,3,FALSE)
K4	K4	=VLOOKUP(K1,'Line Item List'!A1:H4,4,FALSE)
K5	K5	=VLOOKUP(K1,'Line Item List'!A1:H4,5,FALSE)
A8:A55	A8	=LEFT($H$1,4)
B8:B55	B8	=A8&"-"&E8
C8:C55	C8	="'"&B8&"'"
D8:D55	D8	=$K$1
G8:G55	G8	=D8&" | "&F8
H8:H55	H8	=$K$4
I8:I55	I8	=$K$5
J8	J8	=B8
K8:K55	K8	=IFERROR(IF(J8="","",IF(INDIRECT(C8&"!$L$5")="","",INDIRECT(C8&"!$L$5"))),"")
L8:L55	L8	=IFERROR(IF(J8="","",IF(INDIRECT(C8&"!$K$50")="","",INDIRECT(C8&"!$K$50"))),"")
M8:M55	M8	=IFERROR(IF(J8="","",IF(INDIRECT(C8&"!$D$41")="","",INDIRECT(C8&"!$D$41"))),"")
N8	N8	=IFERROR(IF(J8="","",IF(K8="","OPEN - USE CURRENT SOURCE NUMBER","CLOSED - USE NEXT AVAILABLE SOURCE NUMBER")),"")
J9:J55	J9	=IF(N8="","",IF(N8="OPEN - CREATE NEW FORM","",B9))
N9:N55	N9	=IFERROR(IF(J9="","",IF(K9="","OPEN - CREATE NEW FORM","CLOSED - USE NEXT AVAILABLE SOURCE NUMBER")),"")

Named Ranges
Name	Refers To	Cells
'Line Item List'!_FilterDatabase	='Line Item List'!$A$1:$H$21	K2:K5, O1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N8:N55	Cell Value	="CLOSED - USE NEXT AVAILABLE SOURCE NUMBER"	text	NO
N8:N55	Cell Value	="OPEN - CREATE NEW FORM"	text	NO

Test%20Project (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		COLORADO DEPARTMENT OF TRANSPORTATION
2		INSPECTOR'S PROGRESS REPORT
3
4		Project No.:					Project Code (SA#):					Date:
5												12/12/2012
6		No. of Workers:		Equipment:
7
8
9		Location, Comments, and Supporting Calculations:
10		Category:					0100
11		This report is written to document payment for item:					TEST ITEM 1
12
13		Contractor:
14		Description:
15
16
17
18
19
20
21
22
23
24		Comments:
25
26
27
28
29		References:
30		Calculations:
31
32
33
34
35
36
37								Quantity Completed To-Date:				EACH
38								Less Quantity Previously Paid:				EACH
39								Pay Total (This Report):				EACH
40
41		Current Estimate No.:		1				PAY				EACH
42
43
44				Interim		Calculated By:				Measured By:
45
46				Final		Posted By:				Checked By:
47
48
49		Reference No.:		Item No.:		Item Description					Quantity	Unit
50		0000		0000		TEST ITEM 1					50	EACH
51
52
53		The item(s) and material(s) listed above were inspected and found to conform reasonably with the contract plans and specifications, except as noted.
54		Signed By:					Title					No.
55												0000-01
56
0000-01

Cell Formulas
Range	Formula
G10	G10	=VLOOKUP(B50,'Line Item List'!A1:H4,2,FALSE)
G11	G11	=VLOOKUP(B50,'Line Item List'!A1:H4,4,FALSE)
L37	L37	=L50
L38	L38	=L50
L39	L39	=L50
L41	L41	=L50
B50	B50	=LEFT(L55,4)
D50	D50	=VLOOKUP(B50,'Line Item List'!A1:H4,1,FALSE)
F50	F50	=VLOOKUP(B50,'Line Item List'!A1:H4,4,FALSE)
L50	L50	=VLOOKUP(B50,'Line Item List'!A1:H4,5,FALSE)
L55	L55	=MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255)

Named Ranges
Name	Refers To	Cells
'Line Item List'!_FilterDatabase	='Line Item List'!$A$1:$H$21	G10:M11, D50:J50, L50:M50