Hi,

I am having a LOT of trouble putting together this workbook for my company. Basically in the workbook there are two tabs, one titled Master Template Site Basics and another titled Master Template Waste Data Bill. I am sending this workbook out to a lot of global contacts to input information into. On the Master Template Site Basics worksheet there is a list in Column A of the names of all of the global sites our company has. In Column W of that Master Template Site Basics worksheet I have buttons that when clicked, run a macro which makes a copy of the other worksheet, (the Master Template Waste Data bill), so that the individual can fill in additional information for the individual site. What I want is for when that button is clicked and the copy of the Data Bill is made, cell A2 in that new Data Bill sheet to be linked and reflect the same site name that's in Column A of the Master Template Site Basics sheet. I would also like for the tab of that new Data Bill sheet to reflect the same site name. I basically need the button in Column W of the Master Site Basics Template to recognize which row it's in, and pull the contents from Column A of that same row and input them into A2 of the copy of the Data Bill template being made, simultaneously giving that same name to the new tab itself. There will be literally hundreds of sites so I need something that can be easily replicated to be performed on each row. I know Form buttons can be copy and pasted but Command buttons don't work the same way. I don't know if there's some other, better way to handle this, with text boxes or message boxes, etc.. Input and alternative ideas are certainly welcome.

On a separate note, I also need help making a self-updating table of contents so that someone looking to add data to the Data Bill of a particular site can click the site name on the Table of Contents page and be hyperlinked to it. Just for userfriendliness. I have set up a VBA for this but it's running strangely and everytime I click the command button it repastes the names of ALL of the tabs, not just the new ones. Plus the command button and the Table of Contents have to be on a different page for some reason.... Any help on either of these issues would be GREATLY appreciated!! Thank you for your attention and effort in advance. Below please find my code for the macro and my code for the table of contents.




Private Sub CommandButton1_Click()
'
' Sustain Macro
'
' Keyboard Shortcut: Ctrl+x
'
Sheets("Master Template Waste Data Bill").Select
Sheets("Master Template Waste Data Bill").Copy After:=Sheets(3)


End Sub






Private Sub Workbook_Open()
Dim wsht As Worksheet
Dim i As Integer
Dim sText As String
Dim sName As String
Application.ScreenUpdating = False
i = 1
Sheet1.Activate
For Each wsht In ThisWorkbook.Worksheets
Application.ScreenUpdating = False
If wsht.Name <> "MENU" Then
sText = wsht.Name
sName = wsht.Name & "!A1"
Sheets(sText).Activate
ActiveSheet.Range("A1").Select
Sheets(sText).Range("A" & i).Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Menu!A1", TextToDisplay:="MENU"
Sheet1.Range("A" & i).Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
sName, TextToDisplay:=sText
i = i + 1
End If
Sheets("MENU").Activate
ActiveCell.Offset(1, 0).Activate
Application.ScreenUpdating = True
Next wsht

End Sub



Private Sub CommandButton1_Click()
Dim wsht As Worksheet
Dim i As Integer
Dim sText As String
Dim sName As String
i = 1
Sheet1.Activate
For Each wsht In ThisWorkbook.Worksheets
If wsht.Name <> "MENU" Then
sText = wsht.Name
sName = wsht.Name & "!A1"
Sheet1.Range("A" & i).Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
sName, TextToDisplay:=sText
i = i + 1
End If
ActiveCell.Offset(1, 0).Activate
Next wsht


End Sub