PDA

View Full Version : Solved: Adding and Naming a Worksheet using VBA



maxhayden
05-26-2009, 08:30 AM
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!:bow:

HaHoBe
05-26-2009, 08:58 AM
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
05-26-2009, 10:57 AM
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

template sheet is hidden in the attachment

maxhayden
05-27-2009, 03:08 AM
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