Solved: Adding and Naming a Worksheet using VBA

[maxhayden]

Hi there, - I have 2 questions

1. Could anyone tell me how to copy and paste a template worksheet within a workbook and name it using VBA?

2. Is it possible to hide a worksheet (the template used for the new copies) within the workbook so users can't use or edit it?)


Here is the Logic:

1. Copy WorkSheet A
2. Paste NewCopySheet After (SheetCount)
3. ReName NewCopySheet "Link x" (x = 1 for first sheet, x = 2 for 2nd sheet ...)

Thanks in advance for your help!

[HaHoBe]

Hi, maxhayden,

enter the VBIDE and give Visible for that Sheet a 2 - xlSheetVeryHidden. Sheet can only be made visible by using the VBE or by running a macro.

With Worksheets("A")
   .Visible = xlSheetVisible
   .copy after:=Sheets(Sheets.Count)
   ActiveSheet.Name = "Link " & Worksheets.Count
   .Visible = xlSheetVeryHidden
End With

Ciao,
Holger

[lucas]

[VBA]Sub AddNewSheets()
Dim TotalSheets As Variant
TotalSheets = Worksheets.Count - 1
Worksheets("Blank Sheet").Activate
ActiveSheet.Copy after:=Worksheets("Page " & TotalSheets)
Worksheets("Blank Sheet (2)").Activate
ActiveSheet.Name = "Page " & TotalSheets + 1
ActiveSheet.Visible = True

End Sub[/VBA]

template sheet is hidden in the attachment

[maxhayden]

Hi HaBoHe,

Thank you very much for that peice of code. It works perfectly.

I modified it to take into account the other pages in the workbook:

Eg: "Link " & Worksheets.Count - 2

Thanks again!

Max