in VBA : create a worksheet without activate it-CodePudding

I got code which creates(copies) new sheets like:

ActiveWorkbook.Sheets("XXX").Copy_ After:=ActiveWorkbook.Sheets("YYY")

and every time a new Sheet-Copy is created my Excel activates and displays the sheet. How can I turn the auto activation off?

I want to copy new sheets and don't want to auto activate them.

CodePudding user response：

Set the ActiveSheet to an object and reactivate it again at the end of the action:

Sub TestMe()
    
    Dim wks As Worksheet
    Set wks = ThisWorkbook.ActiveSheet
    
    ThisWorkbook.Sheets("XXX").Copy After:=ThisWorkbook.Sheets("YYY")
    
    wks.Activate

End Sub

CodePudding user response：

Try this code

Sub Test()
    Dim ws As Worksheet, sh As Worksheet
    
    Rem Store The Active Sheet Into The Variable [ws] To Activate Later
    Set ws = ActiveSheet
    
    Rem Copy Your Worksheet And The New Worksheet Will Be Active
    ActiveWorkbook.Sheets("XXX").Copy After:=ActiveWorkbook.Sheets("YYY")
    
    Rem You Can Set Another Variable For The New Active Sheet
    Set sh = ActiveSheet
    
    Rem Rename The New Copied Worksheet With Any Name You Desire
    sh.Name = "New Copy"
    
    Rem Finally Activate The First Worksheet Which Was Active
    ws.Activate
End Sub