Copy a Tab and Rename it Based on the First Three Letters of Another Tab

[OilEconomist]

Thanks in advance for any suggestions and I will post feedback as soon as practical.

I would like to copy a template named "TEM.CAT.PHD" and place it before "END.PHD.CFP." I would like to rename the template based on the first three letters of another tab and some additional characters. The tab is located after "START.DTA.PHD". In this case it is "TNM.ALL.PHD".

The following are some examples. So basically if the tab name after START.DTA.PHD is:

• "TNM.ALL.PHD", TEM.CAT.PHD will be copied and placed before "END.PHD.CFP" and renamed "TNM.TOT.CFP."
• "PAS.ALL.PHD", TEM.CAT.PHD will be copied and placed before "END.PHD.CFP" and renamed "PAS.TOT.CFP."
• "EAG.ALL.PHD", TEM.CAT.PHD will be copied and placed before "END.PHD.CFP" and renamed "EAG.TOT.CFP."

The following is my code thus far, but I would like to make it dynamic.

Sub Test()
    
Application.ScreenUpdating = False


'Making the Total Tab
    'Copying the Template
        Worksheets("TEM.CAT.PHD").Copy Before:=Worksheets("END.PHD.CFP")
        On Error Resume Next
        ActiveSheet.Name = "TNM.TOT.CFP"


End Sub

 

[Fluff]

How about

Sub OilEconomist()
   Sheets("TEM.CAT.PHD").Copy Sheets("END.PHD.CFP")
   ActiveSheet.Name = Left(Sheets(Sheets("START.DTA.PHD").Index + 1).Name, 3) & ".TOT.CFP"
End Sub

 

[OilEconomist]

It worked perfectly!

Thank you so much Fluff! You've been a tremendous help!

 

[Fluff]

You're welcome & thanks for the feedback