Macro Help (Of Course)

[Rendon]

This is my Issue:
I have a Macro that creates Worksheets and names them, names are taken from a Pre-Defined Range list in a specific worksheet called Setup.
That macro works perfectly.
___________________________

Sub CreateSheets()
Dim MyCell As Range, MyRange As Range


Set MyRange = Sheets("Setup").Range("B5:B24")
Set MyRange = Range(MyRange, MyRange.End(xlDown))


For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell


Sheets("Setup").Select
End Sub
____________________________

Now I need to insert the name of the Worksheet, to a cell in the corresponding Worksheet using a Macro that I might use, "ALSO", to Format the cell later. obviously, I would like to get the name directly from the list where i created the worksheets.

Here is my problem, I get the correct data enter, but only on the last worksheet. I have not been able to figure out how to reset the values of my counter (if that is the problem).

Any help is appreciated.

____________________________

Sub HeadingInfo()


Dim MyCell As Range
Dim MyRange As Range


Set MyRange = Sheets("Setup").Range("B5:B24")
Set MyRange = Range(MyRange, MyRange.End(xlDown))


For Each MyCell In MyRange


Sheets(Sheets.Count).Select
Range("B2").Select
ActiveCell.FormulaR1C1 = MyCell.Value




Next MyCell


End Sub

____________________________

See image

https://1drv.ms/u/s!AifnZW4FDVY36HBCCy7C4-4Zc3NE

or Download the file

https://1drv.ms/x/s!AifnZW4FDVY36HHGSLnw-cjJlJTu

 

[John_w]

Adapting your first code, try this, untested:

Sub CreateSheets()
Dim MyCell As Range, MyRange As Range

Set MyRange = Worksheets("Setup").Range("B5:B24")

For Each MyCell In MyRange
    Worksheets(MyCell.Value).Range("B2").Value = MyCell.Value
Next

Worksheets("Setup").Select
End Sub

 

[JustynaMK]

Hi Rendon,

If you want to add a sheet name into each of your newly created sheets, try to modify your For loop (HeadingInfo) as follows:

    For Each MyCell In MyRange
        Sheets(MyCell.Value).Range("B2").Value = MyCell.Value
    Next MyCell

Hope it works for you.


+ sorry John, I just saw your answer...

 

[Rendon]

Adapting your first code, try this, untested:

Sub CreateSheets()
Dim MyCell As Range, MyRange As Range

Set MyRange = Worksheets("Setup").Range("B5:B24")

For Each MyCell In MyRange
[B][COLOR=#ff0000]    Worksheets(MyCell.Value).Range("B2").Value = MyCell.Value[/COLOR][/B]
Next

Worksheets("Setup").Select
End Sub

I keep on getting Error Code 9:
subscription out of range when I get to the second place on the loop.

 

[Rendon]

BTW I mixed both responses to try to get one working one... this solution was the closest to work but still came up with error code 9.

 

[Rendon]

BTW I mixed both responses to try to get one working one... this solution was the closest to work but still came up with error code 9.

Sub CreateSheets()
Dim MyCell As Range, MyRange As Range


Set MyRange = Sheets("Setup").Range("B5:B24")
Set MyRange = Range(MyRange, MyRange.End(xlDown))


For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Worksheets(MyCell.Value).Range("B2").Value = MyCell.Value


Next MyCell


Sheets("Setup").Select
End Sub

 

[Rendon]

I figure it out,
after playing around with the code you guys suggested, I came up with the following code that creates the sheets, names them and tag them just like I needed.

Thank you for the suggestions.

Sub CreateSheets()
Dim MyCell As Range, MyRange As Range


Set MyRange = Sheets("Setup").Range("B5:B24")
Set MyRange = Range(MyRange, MyRange.End(xlDown))


For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Range("B2").Select
ActiveCell.FormulaR1C1 = MyCell.Value


Next MyCell


Sheets("Setup").Select
End Sub

 

[JustynaMK]

We're glad it worked for you! Take care.