excel macro

[hermes]

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]

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]

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]

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

 

[hermes]

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