Pulling column value into each previously generated tab

[gradyrickym]

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!

 

[ravishankar]

try adding

ActiveSheet.Name = .Value
ActiveSheet.Range("A3") = c

and see if the output is what you expect
ravishankar