Help with Excel Macro

Solved/Closed
sahkab
Posts
6
Registration date
Monday November 23, 2009
Status
Member
Last seen
March 10, 2010
- Nov 24, 2009 at 05:33 PM
aquarelle
Posts
7115
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 2, 2022
- Nov 24, 2009 at 06:02 PM
Hello,
I am certainly a macro noob and require some help, please, :(
I work at a travel agency and I have a worksheet with all of our reserves. It has alocated columns for: ID numbers, arrival date, departure date, hotel name, number of pax, etc. What I need is a macro that selects the active cell I'm on and adds a new worksheet after the last one, then use that ID number to name the new worksheet. That way I will be able to create a hyper link in my main worksheet and be able to see the details of each reserve that I add. I tried using this code:

Sub AddSheet()
Dim strName As String

strName = Sheets(1).Range("A1")
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = strName
End Sub
As you can see the problem is that it names the new worksheet as the A1 cell. Any ideas? Thx

3 replies

aquarelle
Posts
7115
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 2, 2022
491
Nov 24, 2009 at 05:50 PM
Hello,

Try with this macro :
Sub AddSheet()
Dim strName As String
strName = ActiveCell.Text
    If ActiveCell.Text = "" Then Exit Sub
    For Each sh In Sheets   
        If sh.Name = ActiveCell.Text Then Exit Sub
    Next sh
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = strName
End Sub


Best regards
2
sahkab
Posts
6
Registration date
Monday November 23, 2009
Status
Member
Last seen
March 10, 2010

Nov 24, 2009 at 05:57 PM
Sweet!!! It worked like a charm, just what I was looking for.
Thank you very much aquarelle. :D
0
aquarelle
Posts
7115
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 2, 2022
491
Nov 24, 2009 at 06:02 PM
Happy to have been able to help you :)
0