Change these lines
Code:
Sheets(SheetToCopy).Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = NameTest
to this
Code:
Sheets(SheetToCopy).Copy After:=ActiveSheet
ActiveSheet.Name = NameTest
About the sharing-unsharing
You could incorperate this in your code to perform this task automated, but then you should be shure you are the only user at that time otherwise it could get nasty.