Pulling column value into each previously generated tab

[A10hammond]

I'm new to VBA in excel and I borrowed this code from a previous thread that has worked well. It takes each value from column B in my main tab and creates a named tab with the format of the CONTROL tab and hyperlinks it in the original tab. My problem is that each tab, although correctly named and linked, is filled with data from the CONTROL tab which I have set as the first value in the column. The tab is set up so that all the formulas reference cell A3 which references the value in cell B2 in the Holdings tab. Is there a way so that once the tabs are created, I can run another macro that has the first tab created have cell A3 reference B2 in the holdings tab, the second created tab has cell A3 referencing B3 and so on...? So that each tab is pulling data for the value the tab was named after.

This is the current code I have for excel 2016 on Windows 10:

Sub CreateAndNameWorksheets()
Dim c As Range

Application.ScreenUpdating = False
For Each c In Sheets("Holdings").Range("b2:b47")
Sheets("CONTROL").Copy After:=Sheets(Sheets.Count)
With c
ActiveSheet.Name = .Value
.Parent.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:= _
"'" & .Text & "'!A1", TextToDisplay:=.Text
End With
Next c
Application.ScreenUpdating = True
End Sub


Thanks!

 

[sijpie]

Sub CreateAndNameWorksheets()
    Dim c As Range
    
    Application.ScreenUpdating = False                      ' Don't show all the sheet creation process. Speeds things up
    For Each c In Sheets("Holdings").Range("b2:b47")        ' Loop through the names
        Sheets("CONTROL").Copy After:=Sheets(Sheets.Count)  ' Create a sheet for each name, based on CONTROL
        With c                                              ' the With / End With structure is  a shortcut to avoid having to type a objectname over and over
            ActiveSheet.Name = .Value                       ' Set the new sheet name to the value in the cell
            .Parent.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:= _
            "'" & .Text & "'!A1", TextToDisplay:=.Text      ' Link the cell to the new sheet
            ActiveSheet.Range("A3").Formula = "=Holdings!" & c.Address  ' Set the formula on the new sheet to the cell address
        End With
    Next c                                                  'loop to next cell in column B
    Application.ScreenUpdating = True
End Sub

Also next time use the code tags around any code you post. See below how to do that