VBA for creating a new sheet but staying on previous sheet

[Leo55]

Thought I would be able to do this one, but I over-estimated my abilities.

In my workbook I have one sheet, the title of the sheet changes dependent on the customer reference. -e.g. Cust250885-ref-A485FR etc.

I would like to run a macro for creating a new tab ( when created - it can stay named as sheet1) but I would like the active sheet to stay as the original Customer data as I need the rest of the macro to run on that.

In short how do I create a new sheet but keep the focus on the original sheet but the name of the original sheet can change.

Thank you in advance.

 

[Leo55]

Also if this cannot be done please tell me that I am being too ambitious for my skill level.

Many thanks

 

[NoSparks]

In short how do I create a new sheet but keep the focus on the original sheet but the name of the original sheet can change.

the newly created worksheet will automatically become the active sheet but you can turn screen updating off at the beginning of the macro and then back on at the end after making the original sheet active again.

Sub Test()
    'variable to hold the original active sheet when macro starts
    Dim OrigWS As Worksheet
    Set OrigWS = ActiveSheet
   
    'prevent the screen from updating
    Application.ScreenUpdating = False
    'add the new sheet
    Worksheets.Add After:=Worksheets(Worksheets.Count)
    'name the new sheet
    ActiveSheet.Name = "Whatever"
    'make the original sheet active again
    OrigWS.Activate
    'turn screen updating back on
    Application.ScreenUpdating = True
End Sub

 

[Leo55]

the newly created worksheet will automatically become the active sheet but you can turn screen updating off at the beginning of the macro and then back on at the end after making the original sheet active again.

Sub Test()
    'variable to hold the original active sheet when macro starts
    Dim OrigWS As Worksheet
    Set OrigWS = ActiveSheet
  
    'prevent the screen from updating
    Application.ScreenUpdating = False
    'add the new sheet
    Worksheets.Add After:=Worksheets(Worksheets.Count)
    'name the new sheet
    ActiveSheet.Name = "Whatever"
    'make the original sheet active again
    OrigWS.Activate
    'turn screen updating back on
    Application.ScreenUpdating = True
End Sub

that has worked perfectly! thank you!