Solved: Create, insert, arrange sheet.

[Sir Babydum GBE]

Hi all, I'm working on a new spreadsheet which is designed to facilitate world peace*. I'm stuck though.

Let me explain a little about the structure of this book:
1) There is a sheet named Front page This page will include an index of people, who will each have a sheet named after them.
2) There are two sheets named Start and End which are there so that I can reference them using formulas like =SUM('Start:End'!A1) instead of saying =ThisSheet!A1+ThatSheet!A1+TheOtherSheet!A1 etc etc.
3) There are a couple of summary sheets after the sheet called End
4) There is a sheet named Template that contains formula and formatting used on all the sheets within Start and End

Here's what I need vba help with:

Every Time a name is entered into Column D of Front Page I would like code to Create a new sheet with the First name and the first letter of the Surname (So if I type "Betty Boop" in D1 of Front Page A sheet named "Betty B" would appear in the correct alphabetical position between sheets Start and End
Then I need all the format and formulas/text from Template to be copied to this new sheet - including column widths/row heights etc.

If anyone can help me with this, I will nominate them for a Nobel Peace Prize*

*Not really.

[XLGibbs]

I can help out today, this doesn't sound too bad...any particular layout needed?

If you send me a sample, I can sort it right quick for you

You will need to do one other thing in the set up though...

Define a named range which contains the list of sheet names With Start and End, and use an offset formula to make the list dynamic. As names get inserted (using a sheet change event on the Front Page), they would get added to this list.(inserted below Start and above End)

This named range would appear in your formula using some trickery...

you would then tie your sheet change event to a macro which calls the SortSheets KB entry (with the first part to be modified to insert the new sheet, then sort them between the Start and End)

Sounds simple!

[Sir Babydum GBE]

I can help out today, this doesn't sound too bad...any particular layout needed? If you send me a sample, I can sort it right quick for you

That's great - there's one attached!

You will need to do one other thing in the set up though...

Define a named range which contains the list of sheet names With Start and End, and use an offset formula to make the list dynamic. As names get inserted (using a sheet change event on the Front Page), they would get added to this list.(inserted below Start and above End)

This named range would appear in your formula using some trickery...

you would then tie your sheet change event to a macro which calls the SortSheets KB entry (with the first part to be modified to insert the new sheet, then sort them between the Start and End)

I think I'll stick to accomplishing world peace. I'm sorry mate, you lost me after "dynamic"

Sounds simple!

Whatever!

Thanks my friend!

[XLGibbs]

BD,

How are new names going to be added? Do you want me add this as an option?

The names on the list of Front page have Hyperlinks to locations on their particular sheet (I think)...how do these need to be set...

Are the Start and End sheets used for anything other than bookmarks in the file?

[Sir Babydum GBE]

BD, How are new names going to be added? Do you want me add this as an option?

Well I was gonna just add them manually in column D, then the macro would do the rest for me - but I'm open to suggestions if this is tricky.

The names on the list of Front page have Hyperlinks to locations on their particular sheet (I think)...how do these need to be set...

I wasn't gonna worry about hyperlinking to sheets - they're a relic from last year's sheet. Again - if it's easy to create a link between the new sheet and the name that's just been input then cool - but i'm not worried about leaving them out either.

Are the Start and End sheets used for anything other than bookmarks in the file?

They're there so that I can reference them using formulas like =SUM('Start:End'!A1) instead of saying =ThisSheet!A1+ThatSheet!A1+TheOtherSheet!A1. These formulas will be put in the Summary sheet(s) but I changed them to values before I attached the sheet to this site for data protection reasons.

Thanks again for your help with this.

[XLGibbs]

Okay, I am just gonna hook a brother up then...

[Bob Phillips]

..

[Sir Babydum GBE]

Hi Mr X,

Thanks for your help.

I'm geting a Compile Error and this is being highlighted:

[VBA] Me.Hyperlinks.Add Anchor:=Target, _
Address:="", _
SubAddress:="'" & sName & "'!A1, _
TextToDisplay:=.Value[/VBA]

Any ideas? It says "Syntax Error".

(Sorry to pick holes...)

[Bob Phillips]

That will teach me to make corrections after testing.

[Sir Babydum GBE]

That will teach me to make corrections after testing.

In any case it's much appreciated. It all works well - thanks for your help Mr X and, er, Mr X.

Sir BD

[XLGibbs]

Here you go. Enjoy. I added a drop down list, some named ranges, and some other fun goodies like a custom toolbar.

EDITED: I updated the attachment to include MD's delete code in favor of the original as it is more practical...

[mdmackillop]

Here's some alternative Delete code, in case the menu item is selected from the "wrong" place.

[VBA]
Sub DeleteIndividual()
Dim ans As String, rngNames As Range, strName As String, rngDelete As Range
Dim rngGone As Range
Application.EnableEvents = False
Select Case ActiveSheet.Name
Case "Front Page"
strName = [D7]
Case "Summary"
Sheets("Front Page").Activate
DeleteIndividual
GoTo Exits
Case Else
strName = ActiveSheet.Name
End Select
ans = MsgBox("Sheet '" & strName & "' will be hidden, and the name removed from the list" & _
vbNewLine & "Continue?", vbYesNo, "Alert!")

If ans = vbNo Then GoTo Exits

Application.ScreenUpdating = False

Sheets(strName).Visible = False
Sheets("Front Page").Activate
Set rngNames = Range("ListofNames")
Set rngGone = Range("GoneNames")
rngGone.Offset(rngGone.Rows.Count).Resize(1, 1) = strName
Set rngDelete = rngNames.Find(strName)
If Not rngDelete Is Nothing Then

rngDelete.Delete Shift:=xlUp
End If
'Reset dropdown to first name
[D7] = [I33]
Exits:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

[/VBA]

[XLGibbs]

Thanks MD, that makes more sense, but I spent all of 3 minutes on that particular part of it. That is much more practical for sure...

How bad was the rest of it? I didn't really go hogwild on it, just tried to tie together some elements that seemed useful..

[Sir Babydum GBE]

Everyone,

Thanks for all your help with this. I'm at home now, so I'll have a look at this at work tomorrow.

XLGibbs, I tried your code just before I left work this evening - and unfortunately I got the debug screen on it. But I'll let you know what it said tomorrow.

As I've said, the response to my question has been great, and it is much appreciated. (Did it have anything to do with the Nobel Prize offer?)

[mdmackillop]

Looks OK. I like the dynamic range name. I've never really made use of Offset, but it looks interesting.

Possible additions (but maybe no requirement)
Avoid duplications in the Gone list
Double click a name in the Gone list to resurrect a name.

[XLGibbs]

Ooops. my bad, I left out a line in the toolbar creation....here is a revised version, sans error. it only errored out the 2nd time it opened so I didnt catch it on my test..

[XLGibbs]

Dynamic named ranges are the king. They are particular useful with a lot of what I do for having automatically refreshing data tables that are tied to pivot tables as I define the range dynamically so that whenever it is refreshed the pivot table will be linked to the correct amount of data....

They are quite handy little devils discussed here at length..

http://www.ozgrid.com/Excel/DynamicRanges.htm

[XLGibbs]

Looks OK. I like the dynamic range name. I've never really made use of Offset, but it looks interesting.

Possible additions (but maybe no requirement)
Avoid duplications in the Gone list
Double click a name in the Gone list to resurrect a name.

The Add individual captures this somewhat as if the name already exists, it will unhide and activate it..., but it would be a decent extra to offer the resurrection of said name....

We will see what BD has to say before making any changes so I can address them all at once...

[XLGibbs]

Dear Sir Babydum...any progress or issues to report?

[Sir Babydum GBE]

Dear Sir Babydum...any progress or issues to report?

Sorry about the delay.

It's working fine now thanks - couldn't get back to it til yesterday afternoon. There are features on there that I hadn't even asked for, so I'm most grateful - not just to you - but to everyone who's answered on this post.

Ta very much.