VBA to copy, rename and hyperlink worksheets from master list

[padadof2]

I have a workbook that has a sheet named "Bid Sheet" and on this sheet will have multiple items that I need to copy an existing worksheet and rename it from this list. I would also like to hyperlink the new worksheet to the list on the bid sheet as well as hyperlink a couple cells to the bid sheet from the newly created copy. I have attached a worksheet of what I'm looking for. This is the code I've started. One of the problems I have encountered is that the cost and markups are on different cells depending on the worksheet. I was trying to make something to loop through my visible worksheets and then links them to the bid sheet.

Public Sub CopyOmaxsBidSheets()
    Dim wks As Worksheet
    Set wks = ActiveSheet
    Dim xcell As Range
    Dim xRg As Range
    On Error Resume Next
    Set xRg = Application.InputBox("Please select the items to create bid sheet for:", "Do It", , , , , , 8)
   
    Sheets("0MAX2RE").Visible = True
   
        If xRg Is Nothing Then Exit Sub
            For Each xcell In xRg
                If xcell.Value <> "" Then
                    Sheets("0MAX2RE").Copy After:=Worksheets(Sheets.Count)
                    ActiveSheet.Name = Left(xcell.Value, 30)
                        If Err.Number = 1004 Then
                            ActiveSheet.Name = Right(xcell.Value, 30)
                        End If
                End If
            Next
           
    Sheets("0MAX2RE").Visible = False
End Sub

TestWorkbook.xlsm
	A	B	C	D	E	F	G	H
1	Name
2	location
3	Job
4
5
6
7
8	Item #	Description	Quantity	Units	Unit Cost	Cost	Markup	Total Price
9		Repair Cracks	100	LF	$ -
10		Repair Spalls	150	SF	$ 37.04	$ 5,555.27	0.63	$ 8,817.89
11		Epoxy Overlay	2000	SY	$ -
12		Concrete Bridge Deck Repair, Type 2	125	SF	$ -
13		Concrete Bridge Deck Repair, Type 3	155	SF	$ -
14		Foundation Repairs	1	LS	$ -
15
16		I'd Like to create new worksheets from this list and then hyperlink them, and also automatically link the cost/markup areas
17
Bid Sheet

Cell Formulas
Range	Formula
A1	A1	=NAME
A2	A2	=LOC
A3	A3	=ECMS
G9,G11:G15,G17	G9	=IF(F9="","",0.63)
H9:H15,H17	H9	=IF(F9="","",F9/G9)
G10	G10	='Repair Spalls'!C25
F10	F10	='Repair Spalls'!D22
E17,E9:E15	E9	=IFERROR((F9/C9),"")
B15	B15	=IFERROR(VLOOKUP(item7, #REF!,4,FALSE),"")
D15	D15	=IFERROR(VLOOKUP(item7, #REF!,3,FALSE),"")
B17	B17	=IFERROR(VLOOKUP(item9, #REF!,4,FALSE),"")
D17	D17	=IFERROR(VLOOKUP(item9, #REF!,3,FALSE),"")

Named Ranges
Name	Refers To	Cells
ECMS	=Index!$C$8	A3
item7	='Bid Sheet'!$A$15	D15, B15
item9	='Bid Sheet'!$A$17	D17, B17
LOC	=Index!$C$7	A2
NAME	=Index!$C$6	A1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3	Cell Value	=0	text	NO

TestWorkbook.xlsm
	B	C	D
21	MARK-UP	0.53	11158.50
22		0.63	9387.31
Crack Repair

Cell Formulas
Range	Formula
D21	D21	=D19/C21
D22	D22	=D19/C22

The markup and cost areas area always to the right of the "MARK-UP" text, so I thought I could loop through my visible sheets and then link them? Hopefully this makes sense. Thanks in advance for any assistance.

 

[padadof2]

Anyone?

 

[padadof2]

Is this possible?