VBA Code to copy Hidden Worksheet

suet

Board Regular
Joined
Oct 19, 2005
Messages
56
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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:
VBA Code:
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:
VBA Code:
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
----- --
 
Upvote 0
Thanks for posting on the forum.

Let's check what you have:

Ok


Assign one of the following macros to each button:
VBA Code:
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:
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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
Back
Top