Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sheets('MASTER').Copy After:=Sheets(33) (97)

    I'd like to be able to copy a sheet and paste a copy of it after the last sheet in the wb.
    When recording the macro I noticed that it used the following code,

    Sheets("MASTER").Copy After:=Sheets(33)

    So I need to know how to replace the "=sheets(33)" bit with some "after last sheet" code.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sheets('MASTER').Copy After:=Sheets(33) (97)

    Youi can replace the "=sheets(33)" bit with =Sheets(Sheets.Count) so that you have :<pre>Sheets("MASTER").Copy After:=Sheets(Sheets.Count)</pre>

    Andrew C

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheets('MASTER').Copy After:=Sheets(33) (97)

    Thanks Andrew,

    What if I also wanted the sheets to automatically rename themselves -just simple names such as 1, 2, 3 etc...

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheets('MASTER').Copy After:=Sheets(33) (97)

    In <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=54774&page=&view= &sb=&o=&vc=1> this thread </A> you can find a macro that automatically numbers a sheet with the name you want when you create it. To rename all sheets in a workbook, you can use code like this:

    <pre>Sub RenameAllSheets()
    Dim q As Integer
    Dim oSheet As Worksheet
    q = 0
    For Each oSheet In Worksheets
    q = q + 1
    oSheet.Name = "Sheet" & q
    Next
    End Sub
    </pre>


Posting Permissions

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