VBA Code to copy Hidden Worksheet

[suet]

Hi All

Happy Wednesday to you all.

I have a workbook with a number of hidden sheets: eg: Emp Ins , Practical Comp, Final Completion.. All these worksheets are hidden.

I have a worksheet that is called Main Menu and it has buttons on it that you click to add a New Employers Instruction, or Final Completion or Practical Completion.

However, I can't get the code to work that will look for the hidden worksheet, eg: Emp Ins and then make a copy of it and name the tab Emp Ins 1. If I then click the button to add another Emp Ins I need the VBA code to create another copy of Emp Ins but assign it number 2 as I have already created Emp Ins 1.

I hope the above makes sense.

The code I have used is as follows:

Sub ShowHideEmpInstruction()

Sheets("Emp Ins").Visible = False


'Make copy of master'
Sheets("Emp Ins").Copy After:=Sheets("Schedule of Payments")
Sheets("Emp Ins(2)").Visible = True
Sheets("Emp Ins (2)").Select
End Sub

However, it just goes to the last worksheet and erases all the data and just add number 2 to the cell.

Any help/guidance would be greatly appreciated.

Thanking you in advance

Regards
Sue

 

[DanteAmor]

Thanks for posting on the forum.

Let's check what you have:

I have a workbook with a number of hidden sheets: eg: Emp Ins , Practical Comp, Final Completion.. All these worksheets are hidden.

Ok

I have a worksheet that is called Main Menu and it has buttons

Assign one of the following macros to each button:

Sub Emp_Ins()
  Call CopySheet("Emp Ins")           'Fit to the name of your sheet to copy.
End Sub

Sub Pra_Com()
  Call CopySheet("Practical Comp")    'Fit to the name of your sheet to copy.
End Sub

Sub Fin_Com()
  Call CopySheet("Final Completion")  'Fit to the name of your sheet to copy.
End Sub

Put the above macros in a module.

In the same module put the following macro:

Sub CopySheet(TabName)
  Dim newSh As Worksheet, sh As Worksheet
  Dim i As Long
 
  Application.ScreenUpdating = False
  'Make copy of master'
  With Sheets(TabName)
    .Visible = True
    .Copy After:=Sheets("Schedule of Payments")
    Set newSh = ActiveSheet
    newSh.Name = "waitingforaname"
    .Visible = False
  End With
 
  For Each sh In Sheets
    If Left(LCase(sh.Name), Len(TabName)) = LCase(TabName) Then
      i = i + 1
    End If
  Next
 
  newSh.Name = TabName & " " & i
  Application.ScreenUpdating = True
End Sub

The "CopySheet" macro will work with any sheet. What it does is receive the sheet you want to copy as a parameter, it makes it visible, copies it, hides it again, renames the new sheet with the name and the corresponding consecutive, for example "Emp Ins 1", then "Emp Ins 2" and so on.

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[suet]

Thanks for posting on the forum.

Let's check what you have:

Ok


Assign one of the following macros to each button:

Sub Emp_Ins()
  Call CopySheet("Emp Ins")           'Fit to the name of your sheet to copy.
End Sub

Sub Pra_Com()
  Call CopySheet("Practical Comp")    'Fit to the name of your sheet to copy.
End Sub

Sub Fin_Com()
  Call CopySheet("Final Completion")  'Fit to the name of your sheet to copy.
End Sub

Put the above macros in a module.

In the same module put the following macro:

Sub CopySheet(TabName)
  Dim newSh As Worksheet, sh As Worksheet
  Dim i As Long
 
  Application.ScreenUpdating = False
  'Make copy of master'
  With Sheets(TabName)
    .Visible = True
    .Copy After:=Sheets("Schedule of Payments")
    Set newSh = ActiveSheet
    newSh.Name = "waitingforaname"
    .Visible = False
  End With
 
  For Each sh In Sheets
    If Left(LCase(sh.Name), Len(TabName)) = LCase(TabName) Then
      i = i + 1
    End If
  Next
 
  newSh.Name = TabName & " " & i
  Application.ScreenUpdating = True
End Sub

The "CopySheet" macro will work with any sheet. What it does is receive the sheet you want to copy as a parameter, it makes it visible, copies it, hides it again, renames the new sheet with the name and the corresponding consecutive, for example "Emp Ins 1", then "Emp Ins 2" and so on.

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Hi Dante

Thanks for the quick response. I will try the above and come back to you as soon as I can.

Many thanks for your help.

Regards
Sue