Excel VBA code o hide a worksheet once copied and saved

[Tony_Y]

Good evening,

Thanks to this forum and it's members I have a code which copies and saves a worksheet, naming it he value of a cell from another worksheet. I am now after taking this a little further and:

1. Hiding the newly saved sheet and
2. Unhiding and activating sheet 16.

Any help will be greatly appreciated

TIA

Tony

VBA Code

Private Sub CommandButton2_Click()

   Dim shtName As String
  
   shtName = Sheet22.Range("D64").Value

   If Evaluate("isref('" & shtName & "'!A1)") Then

      MsgBox "Sheet Already Exists"

   Else
      Sheet14.Copy , Sheets(Sheets.Count)

      ActiveSheet.Name = shtName

      Sheet14.Activate

      Range("E9:E19,E38:E46,K9:K16,K38:K39").Value = 0

      MsgBox "Sheet Saved Success"

      Sheet16.Activate
     

   End If

End Sub

 

[Fluff]

How about

      Sheet14.Copy , Sheets(Sheets.Count)

      With ActiveSheet
         .Name = ShtName
         .Visible = xlSheetHidden
      End With
      Sheet14.Range("E9:E19,E38:E46,K9:K16,K38:K39").Value = 0

      MsgBox "Sheet Saved Success"

      Sheet16.Visible = xlSheetVisible

 

[Tony_Y]

Hi Fluff,

That works great very many thanks (sorry for late reply - working).

kind regards

Tony

 

[Fluff]

You're welcome & thanks for the feedback.