Copying worksheets and renaming

[Blsgw]

Hello everyone, I have seen a lot of posts with this issue, but none with the exact answer to my problem.

Im trying to make a macro that Copies a worksheet named "Brand Template", then moves the copy to the end of all my worksheets, and renames it to an input cell that at the moment is located in worksheet "Brands" cell C5

Now if I could get a code that does that, fantastic... but what would be ever better is a code that copies the "Brand Template" worksheet, moves it to the end of all worksheets, and brings up an Input window with a tooltip... that would be amazing. Any help would be fantastic :D

While Im here.. Another problem I have, In my workbook, I have a lot of different recipes (over 100) and each recipe has a different brand (around 15 brands in total).

Im trying to create a costings sheet that has the ability to have a drop down menu to pick the brand, then another drop down menu that displays the products that brand has (which I have managed to do) but the problem I have, is importing the ingredients from said recipe that i select into the worksheet (if this is just confusing you let me know)

 

[VoG]

Try

Sub CopySheet()
Sheets("Brand Template").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Sheets("Brands").Range("C5").Value
End Sub

 

[Blsgw]

Works perfectly, thank you :D

 

[Blsgw]

New question, If i was trying to get the input data ("Brands C5") and copy it to a list that is at "Brand List" A4 downwards how would i go about this in an easy way. The list will obviously be every growing so wondering how I would be able to get the data from Brands C5 to go to the bottom of the list? that could be anywhere from A4 to up to A20

 

[VoG]

Try this: right click the Brands sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C5" Then Sheets("Brand List").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Target.Value
End Sub