+ Reply to Thread
Results 1 to 8 of 8

MAcro to copy and insert a sheet

  1. #1
    JackR
    Guest

    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?

  2. #2
    JE McGimpsey
    Guest

    Re: MAcro to copy and insert a sheet

    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.


  3. #3
    JackR
    Guest

    Re: MAcro to copy and insert a sheet

    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.

    >


  4. #4
    Tom Ogilvy
    Guest

    Re: MAcro to copy and insert a sheet

    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?




  5. #5
    JackR
    Guest

    Re: MAcro to copy and insert a sheet

    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?

    >
    >
    >


  6. #6
    JE McGimpsey
    Guest

    Re: MAcro to copy and insert a sheet

    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


  7. #7
    Tom Ogilvy
    Guest

    Re: MAcro to copy and insert a sheet

    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?

    > >
    > >
    > >




  8. #8
    JackR
    Guest

    Re: MAcro to copy and insert a sheet

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

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1