Help with Excel Macro Solved/Closed

Question

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

aquarelle · Accepted Answer

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

sahkab · Answer

Sweet!!! It worked like a charm, just what I was looking for.
Thank you very much aquarelle. :D

aquarelle · Answer

Happy to have been able to help you :)

Help with Excel Macro

3 responses

Similar discussions

Subscribe To Our Newsletter!