excel macro

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
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
 

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.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
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
 

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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Top