MAcro to copy and insert a sheet

[Guest]

I want to asigna macro to a button that will copy a hiden sheet, and paste it
in a certain spot among among other sheets, Example;

I have the following sheets: (these are recipes)
Chicken soup
Beef Soup
Potato soup
Clam soup

What I need is to insert the new sheet after potato soup and clam soup,
keeing in mind that the names of the recipes will continually change.

Is this clear? Any help or ideas?

 

[JE McGimpsey]

It's not clear to me how you could insert one sheet after both potato
soup and clam soup...

What if the sheets were rearranged so that clam soup was first?

 

[Guest]

It would insert between Potato and clam soup

It's not clear to me how you could insert one sheet after both potato
soup and clam soup...

What if the sheets were rearranged so that clam soup was first?

 

[Tom Ogilvy]

In general:

Sub abc()
Dim sh As Worksheet, sh1 As Worksheet
Set sh = Worksheets("Template")
sh.Visible = xlSheetVisible
sh.Copy After:=Worksheets("Potato soup")
Set sh1 = ActiveSheet
sh.Visible = xlSheetHidden
sh1.Name = "ABC"
End Sub

so if you mean before the last sheet

Sub abc()
Dim sh As Worksheet, sh1 As Worksheet
Set sh = Worksheets("Template")
sh.Visible = xlSheetVisible
sh.Copy Before:=Worksheets(Worksheets.count)
Set sh1 = ActiveSheet
sh.Visible = xlSheetHidden
sh1.Name = "ABC"
End Sub

 

[Guest]

Your second option almost works, I get an error on this line" sh1.Name = "ABC"
also what if I wanted it at the end of all the sheets rather than befroe the
last sheet?
Maybe I did not understand your reference to "ABC"??

 

[JE McGimpsey]

And if the sheets were rearranged so that clam soup was the first sheet
and potato soup the last?

 

[Tom Ogilvy]

Sub abc()
Dim sh As Worksheet, sh1 As Worksheet
Set sh = Worksheets("Template")
sh.Visible = xlSheetVisible
sh.Copy After:=Worksheets(Worksheets.count)
Set sh1 = ActiveSheet
sh.Visible = xlSheetHidden
End Sub

 

[Guest]

Thank you that works great

Sub abc()
Dim sh As Worksheet, sh1 As Worksheet
Set sh = Worksheets("Template")
sh.Visible = xlSheetVisible
sh.Copy After:=Worksheets(Worksheets.count)
Set sh1 = ActiveSheet
sh.Visible = xlSheetHidden
End Sub