MAcro to copy and insert a sheet

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?

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?

In article <91EC4A72-C076-4EEC-8760-6B4A45BACF74@microsoft.com>,
JackR <JackR@discussions.microsoft.com> wrote:

> 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.

It would insert between Potato and clam soup

"JE McGimpsey" wrote:

> 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?
>
> In article <91EC4A72-C076-4EEC-8760-6B4A45BACF74@microsoft.com>,
> JackR <JackR@discussions.microsoft.com> wrote:
>
> > 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.
>

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

--
Regards,
Tom Ogilvy

"JackR" <JackR@discussions.microsoft.com> wrote in message
news:91EC4A72-C076-4EEC-8760-6B4A45BACF74@microsoft.com...
> 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?

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 Before:=Worksheets(Worksheets.count)
> Set sh1 = ActiveSheet
> sh.Visible = xlSheetHidden
> sh1.Name = "ABC"
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "JackR" <JackR@discussions.microsoft.com> wrote in message
> news:91EC4A72-C076-4EEC-8760-6B4A45BACF74@microsoft.com...
> > 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?
>
>
>

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

In article <DA8DA7B9-FC09-4E53-A0D0-9AF68972E885@microsoft.com>,
JackR <JackR@discussions.microsoft.com> wrote:

> It would insert between Potato and clam soup

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" <JackR@discussions.microsoft.com> wrote in message
news:E5B96088-6A32-4E62-B2E8-DAFAF0229918@microsoft.com...
> 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 Before:=Worksheets(Worksheets.count)
> > Set sh1 = ActiveSheet
> > sh.Visible = xlSheetHidden
> > sh1.Name = "ABC"
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "JackR" <JackR@discussions.microsoft.com> wrote in message
> > news:91EC4A72-C076-4EEC-8760-6B4A45BACF74@microsoft.com...
> > > 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?
> >
> >
> >

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" <JackR@discussions.microsoft.com> wrote in message
> news:E5B96088-6A32-4E62-B2E8-DAFAF0229918@microsoft.com...
> > 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 Before:=Worksheets(Worksheets.count)
> > > Set sh1 = ActiveSheet
> > > sh.Visible = xlSheetHidden
> > > sh1.Name = "ABC"
> > > End Sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "JackR" <JackR@discussions.microsoft.com> wrote in message
> > > news:91EC4A72-C076-4EEC-8760-6B4A45BACF74@microsoft.com...
> > > > 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?
> > >
> > >
> > >
>
>
>