CODEWITHSUNDEEP
vbaexcel
Appy
Appy

Reputation: 21

How to create instances of hidden worksheet in excel using vba?

I am creating an macro-enabled Excel as a tool for generating 'create table' sql script. The sheet is created where one needs to enter the column names, data type etc., and on a button click the script will be generated. This sheet is called 'Script Generator'. Now I need an 'Index' sheet which will have table names and a button. When I click the button I need to open 'script generator' sheets for each table name and these sheets should be renamed to the table name.

The index sheet code goes like this:

Sub Add_sheets()
On Error Resume Next
Dim count As Integer
Dim r As Range
Dim sh As Worksheet

For Each r In Range("A3:A103")
If Not r = "" Then
  count = 0
For Each sh In ActiveWorkbook.Sheets
 If sh.Name = r.Value Then
   count = count + 1
 End If
Next sh
If count = 0 Then
With ActiveWorkbook.Sheets
.Add(after:=Worksheets(Worksheets.count), Type:="C:\Macro\Script_Template.xltm").Name =  r.Value
End With

ActiveSheet.Hyperlinks.Add Anchor:=r, Address:="", _
SubAddress:=Sheets(r.Value).Name & "!A1"
End If
End If
Next r
End Sub

Now, the problem is I am adding the script generator saved as 'Script_Template.xltm' externally. I need only one Excel which will do this all. Means, the Index file should internally open/add the new sheets of the format 'script generator' so that it forms one complete tool. Maybe by hiding this sheet and calling its instances through macros and renaming those sheets. How to do it through VBA? Could someone help me with this?

Upvotes: 2

Views: 10280

Answers (2)

Mozan Sykol
Mozan Sykol

Reputation: 367

Using True and False for setting the Visible property of a worksheet is not good practice. You should use the constants already provided - xlSheetHidden, xlSheetVeryHidden and xlSheetVisible.

xlSheetVisible will make your sheet visible and xlSheetHidden will hide your sheet. Setting it to xlSheetVeryHidden will ensure that the only way you can make the sheet visible is through VBA and not through the Excel menubar Format -> Sheet -> Unhide.

Usage:

Sheets("Script_Template").Visible = xlSheetVisible

Upvotes: 3

Siddharth Rout
Siddharth Rout

Reputation: 149287

You can create a "Script_Template" sheet and hide it and then use this code create a copy

Sheets("Script_Template").Visible = True
Sheets("Script_Template").Copy After:=Sheets(ThisWorkbook.Sheets.count)
ActiveSheet.Name = r.Value
Sheets("Script_Template").Visible = False

Upvotes: 2

Related Questions