Excel VBA Code to hide a sheet once copied

[Tony_Y]

Good evening,

I have a VBA code which works great thanks to members on this forum. I wish to hide the sheet once it is created and am seeking the code to do so. Below is he code I already have. Any assistance will be greatly appreciated. TIA

Tony

VBA Code

Private Sub CommandButton2_Click()
   Dim ShtName As String
  
   ShtName = Sheet22.Range("D55").Value
   If Evaluate("isref('" & ShtName & "'!A1)") Then
      MsgBox "Sheet Already Exists"
   Else
      Sheet14.Copy , Sheets(Sheets.Count)
      ActiveSheet.Name = ShtName
      Sheet14.Activate
      Range("E9:E36,E38:E46,K9:K25,K38:K52").Value = 0
      MsgBox "Sheet Saved Success"
      Sheet16.Activate
     
   End If
End Sub

 

[Fluff]

You can use

Sheets(ShtName).Visible=xlSheetHidden

 

[Tony_Y]

Hi Fluff

thanks for the prompt response. I am getting a runtime error 13 type mismatch on my original code. When I debug its highlighting as shown:

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:E36,E38:E46,K9:K25,K38:K52").Value = 0
MsgBox "Sheet Saved Success"
Sheet16.Activate
Sheets(ShtName).Visible = xlSheetHidden

End If

End Sub

 

[Fluff]

Maybe change it back to looking at D55?

 

[Tony_Y]

Hi Fluff,

sorry for the very late reply. I changed D55 to D64 as I had moved the location on Sheet 22. I have now sorted it. I don't quite know how but it eventually showed debug on the line where I reset the values back to 0. I had locked some of the cells within sheet 14 so i unlocked them and all is good - it saves the worksheet with the correct name and thanks to you hides the sheet.

Once again many thanks for this.

Kind regards

Tony

 

[Fluff]

Glad you sorted it & thanks for the feedback.