1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

excel macro

Discussion in 'Business Applications' started by hermes, Jan 28, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. hermes

    hermes Thread Starter

    Joined:
    Aug 12, 2000
    Messages:
    642
    Hi

    I have been playing around with a macro I was shown at College many moons ago before I realised I would never be any good at VBA.

    The macro simply unhides a protected worksheet and copies it to the end of the workbook, then displays an input box asking you to name the sheet.

    After remembering why I was no good at VBA for a few hours, I got it all working except 1 thing....The new worksheet won't move to the end. The first one does, but the rest go in front of this one.

    The vba code follows, I suspect the problem is with the "Copy After:=Sheets(10)" line, but have lost patience.
    .....................................................................................................
    Dim NewPageName As String
    Sub NewPage()
    '
    ' NewPage Macro
    ' Macro recorded 28/01/2003 by
    '

    '
    Sheets("Master").Visible = True
    Sheets("Master").Copy After:=Sheets(10)
    Sheets("Master").Select
    ActiveWindow.SelectedSheets.Visible = False
    NewPageName = InputBox("What would you like to call your new Worksheet")
    ActiveWindow.ActiveSheet.Name = NewPageName


    End Sub
    .......................................................................................................
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Try this or something like it.
    I didn't test it.


    Sheets("Master").Visible = True
    Sheets("Master").Move after:=Worksheets(Worksheets.Count)
    Sheets("Master").Select
    ActiveWindow.SelectedSheets.Visible = False
    NewPageName = InputBox("What would you like to call your new Worksheet")
    ActiveWindow.ActiveSheet.Name = NewPageName


    When I'm trying to find code, I'll actually paste a piece of it into Google search. For yours I searched for:

    .Copy After:=Sheets

    And I found this page:

    http://www.mindspring.com/~tflynn/excelvba3.html
     
  3. hermes

    hermes Thread Starter

    Joined:
    Aug 12, 2000
    Messages:
    642
    Hi Dreamboat

    Bizaarely, when I try this code it just renames the current sheet and does not produce a new one. I tried a few changes to the code but no luck.

    When I tried the other example from your link, the sheets did not rename or move to the end. I suppose I could declare a variable (x), use this as a counter and increment it every time a new sheet is created then copy the new sheet after (x) number of sheets.

    That is about the limit of my VB skills i'm afraid.
     
  4. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Okay, here ya go:

    Dim NewPageName As String

    Sub NewPage()

    Sheets("Master").Visible = True
    Sheets("Master").Copy After:=Worksheets(Worksheets.Count)
    NewPageName = InputBox("What would you like to call your new Worksheet")
    ActiveWindow.ActiveSheet.Name = NewPageName
    Sheets("Master").Visible = False

    End Sub



    :D
     
  5. hermes

    hermes Thread Starter

    Joined:
    Aug 12, 2000
    Messages:
    642
    Hi dreamboat

    I got it going with 1 small addition to the code. The new sheet has to be selected before the name change or the sheetas keep adding, but the name of the first one only, changes.

    Thanks for the help. I've one more niggle, but i'll post it separately.

    Sheets("Master").Visible = True
    Sheets("Master").Copy After:=Worksheets(Worksheets.Count)
    Sheets("Master").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Master (2)").Select
    NewPageName = InputBox("What would you like to call your new Worksheet")
    ActiveWindow.ActiveSheet.Name = NewPageName
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/115742

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice