Renaming cell in sheet 2:n from range in sheet1

[lavinius]

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.

Please Login or Register  to view this content.

Many thanks!

[lavinius]

Found Solution for my problem by adding

Please Login or Register  to view this content.

As the main Range is on AD7 and the range i want for B1 in every new sheet is 2 steps to the right in AF7.

Cheers!