Hello!

Iv been looking for a solution to a specific task, and have come upp empty.

I have a Main sheet wich has a list in range AF7:AF999, The range varies as it is autogenerated from folder dir.

I have a macro that then autogenerates n number of sheets as a copy of sheet 2 and renames them from a second list.
As the sheets are autogenerated i would like to rename the header, in this case cells B1:I2 (Merged cells) from the list in range AF7 and down, in the order they are created. for example

List in range AF7 and down might be

Bike
Car
Plane

The first copy of sheet 2 that is auto generated should in cell B1:I2 Have the value "Bike"
Second Sheet should have "Car"
and so on. Would be nice to have this in the code that aoutgenerates the sheets.

The code is as follows.

Sub AutoAddSheet()

Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("NCC_DataTool").Range("AD7")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
    Sheets(9).Copy After:=Sheets(Sheets.Count) 'Create a new worksheet
    Sheets(Sheets.Count).Name = MyCell.Value 'Renames the new worksheets
    
    Next MyCell
End Sub

Many thanks!