VBA Code to add a sheet after another errors out...

[bbbb1234]

I am attempting to add a new sheet after a specific sheet. Best I can remember (it has been some time back), it worked in standalone mode in a short test macro but now that I have integrated the code below with the rest of the macro code, it is not working. It compiles fine but at runtime, it errors out - error screenshot below. To debug, I put the code in a test macro, as noted below. Again, it still errors out on the same NOTED line of code. I cannot for the life of me figure this one out.

I am also wide open to any code cleanup that is offered to improve flow, make it run faster, or anything else of the sort.

PLEASE HELP!!!
-----------------------------------------------------------------------------------------------------------------------------

Sub test()

Dim LastName As String
Dim FirstName As String
Dim FullName As String

Sheets("Patient").Select
LastName = "ALast1"        'this is a dummy line to set the variable in the test macro
FirstName = "AFirst1"'       this is a dummy line to set the variable in the test macro
FullName = LastName & ", " & FirstName
Sheets.Add After:=Sheets("DON'T REMOVE THIS TAB2").Name = FullName       '<-----------------------THIS IS THE LINE THAT THROWS THE SCREENSHOTTED ERROR BELOW
Worksheets("LOG TAB MASTER").Range("a1:XFD1048576").Copy
ActiveSheet.Paste Destination:=Worksheets(LastName & ", " & FirstName) _
    .Range("a1:XFD1048576")
    Sheets(LastName & ", " & FirstName).Select
With ActiveWorkbook.Sheets(LastName & ", " & FirstName).Tab _
    .ColorIndex = xlColorIndexNone
    End With

End Sub

Error Screenshot

 

[Alex Blakenburg]

Try adding brackets to that line.

Sheets.Add(After:=Sheets("DON'T REMOVE THIS TAB2")).Name = FullName

 

[Herakles]

I've taken the liberty to rewrite your procedure.

Sub test()
Dim Wb As Workbook
Dim WsName As Worksheet

    Set Wb = ActiveWorkbook
            
    Wb.Worksheets("LOG TAB MASTER").Copy after:=Wb.Worksheets("DON'T REMOVE THIS TAB2")
    
    Set WsName = ActiveSheet
    
    With WsName
        .Name = "ALast1," & "AFirst1"
        .Tab.ColorIndex = xlColorIndexNone
    End With
    
End Sub

 

[Peter_SSs]

@bbbb1234
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[bbbb1234]

Alex Blakenburg and Herakles​

I could not figure out how to quote both of your posts. Thanks for the replies! Both of these worked!!!

 

[bbbb1234]

Peter_SSs​

Thanks for the hints for tags!

 

[Alex Blakenburg]

Alex Blakenburg and Herakles​

I could not figure out how to quote both of your posts. Thanks for the replies! Both of these worked!!!

Thanks for testing and providing feedback on both suggestions. Glad we could help.