ExcelBanter - View Single Post

JackR

Thank you that works great

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"JackR" wrote in message
...
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"??
"Tom Ogilvy" wrote:

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 Befo=Worksheets(Worksheets.count)
Set sh1 = ActiveSheet
sh.Visible = xlSheetHidden
sh1.Name = "ABC"
End Sub

--
Regards,
Tom Ogilvy

"JackR" wrote in message
...
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?