Excel VBA Code to hide a sheet once copied

Tony_Y

New Member
Joined
May 29, 2020
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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

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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
You can use
VBA Code:
Sheets(ShtName).Visible=xlSheetHidden
 

Tony_Y

New Member
Joined
May 29, 2020
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Maybe change it back to looking at D55?
 

Tony_Y

New Member
Joined
May 29, 2020
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,931
Messages
5,621,666
Members
415,849
Latest member
PhoenixRising2015

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top