Use Current Worksheet Name in Formula to Rename Newly Created Worksheet

**MICowboy13** · 06-12-2015, 03:21 PM

Hi,

I am working with the following part of code;

Machs = Array("Mach-1", "Mach-2", "Mach-3", "Mach-4", "Mach-5", "Mach-6", "Mach-7", _
         "Mach-8", "Mach-9", "Mach-10", "Mach-11", "Mach-12")

For Each Mach In Machs
    If Range("O236") > 0 Then
    Worksheets.Add(After:=Worksheets(1)).Name = "My New Worksheet"
Next Mach

What would i replace "My New Worksheet" with to get the current worksheet name (see array above) plus the word "Budget"?

Thanks

**reagan.kha** · 06-12-2015, 03:27 PM

Try this -

sheetname = ActiveSheet.Name
Worksheets.Add(After:=Worksheets(1)).Name = sheetname & " Budget"

**JOHN H. DAVIS** · 06-12-2015, 03:27 PM

Maybe:

Sub MICowboy13()
Machs = Array("Mach-1", "Mach-2", "Mach-3", "Mach-4", "Mach-5", "Mach-6", "Mach-7", _
         "Mach-8", "Mach-9", "Mach-10", "Mach-11", "Mach-12")

For Each Mach In Machs
    If Range("O236") > 0 Then
    Worksheets.Add(After:=Worksheets(1)).name = Mach & " Budget"
Next Mach

End Sub

**MICowboy13** · 06-12-2015, 03:42 PM

Reagan.kha,

Thank you for your response, your solution may have worked but John's made more sense to me so I went with that one.

John,

Thanks for your help. If you have a spare moment can you review my code and tell me why it stops after the first worksheet in my array? After running the code it creates a new sheet and names it "Mach-1 Budget" and then nothing else happens. Also the code only works if I run the macro from the sheet named "Mach-1". What am I doing wrong?

Sub CreateBudgetForm()

'Misc
Dim Machs As Variant
Dim Mach As Variant


'Loop through sheets
Machs = Array("Mach-1", "Mach-2", "Mach-3", "Mach-4", "Mach-5", "Mach-6", "Mach-7", _
         "Mach-8", "Mach-9", "Mach-10", "Mach-11", "Mach-12")

For Each Mach In Machs
    If Range("O236") > 0 Then Worksheets.Add(After:=Worksheets(1)).Name = Mach & " Budget"
Next Mach

End Sub

Thanks

**JOHN H. DAVIS** · 06-12-2015, 04:00 PM

From what I see Mach should be declared as a worksheet. And Machs should be a Sheets Array. I don't have much time as I'm signing off for the weekend. If you haven't got a solution by Monday, I'll be glad too revisit.

**MICowboy13** · 06-12-2015, 04:26 PM

John,

Thanks for your help and have a great weekend.

Use Current Worksheet Name in Formula to Rename Newly Created Worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Use Current Worksheet Name in Formula to Rename Newly Created Worksheet

Re: Use Current Worksheet Name in Formula to Rename Newly Created Worksheet

Re: Use Current Worksheet Name in Formula to Rename Newly Created Worksheet

Re: Use Current Worksheet Name in Formula to Rename Newly Created Worksheet

Re: Use Current Worksheet Name in Formula to Rename Newly Created Worksheet

Re: Use Current Worksheet Name in Formula to Rename Newly Created Worksheet

Thread Information

Users Browsing this Thread

Similar Threads

Copy Rows by Cell Value, Paste to Newly Created Worksheet, Email Contents

Create Multiple sheets, rename and copy the contents of a Range to newly created sheet

Updating data on newly created worksheet

macro that will automatically link a newly created worksheet to a summary sheet

Referencing a newly created worksheet

Bookmarks

Bookmarks

Posting Permissions