Excel: VBA create worksheets without duplicates

Question

I've been trying to create a macros for creating worksheets. The code should do the following:

1) Create worksheets for ColumnB from Master sheet, using template from "Template" worksheet.

2) The range of ColumnB in Master sheet is variable, but this is my first try with excel-vba, and I don't know how to set a variable range.

3) Rename each worksheet as per the name in each cell in ColumnB

3.1) ColumnB has duplicate entries, but we need to create only 1 worksheet for duplicate cells. (deleting duplicates is not an option)

4)Hyperlink the worksheets to the cells in the Column B of Master sheet.

I am facing issues with the point 3.1 mentioned above. Below is the closest thing I found useful: Can we refine it to my requirements?

Sub CreateAndNameWorksheets()
    Dim c As Range

    Application.ScreenUpdating = False
        For Each c In Sheets("Master").Range("B5:B25000")
        Sheets("Template").Copy After:=Sheets(Sheets.Count)
        With c
            ActiveSheet.Name = .Value
            .Parent.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:= _
                "'" & .Text & "'!A1", TextToDisplay:=.Text
        End With
    Next c
    Application.ScreenUpdating = True

End Sub

Thank you!

The SheetExists function in this answer will help. So first, test if sheet already and if so move on to next cell. — Scott Holtzman
– Scott Holtzman, Commented Dec 5, 2016 at 15:03

Community · Accepted Answer · 2017-05-23 12:08:41Z

0

Solution explanation:
While the SheetExists is a neat approximation to solve the issue stated, the real solution would be more complicated than that
Solution:
The Sub Duplicate_Template will help you to do so. And is easier to call it whenever you need to do the same operation (I call this "mirror functions").

Sub Duplicate_Template(TemplateToDuplicate As String, NameNewSheet As String)
    If SheetExists(NameNewSheet) = False Then
    Sheets(TemplateToDuplicate).Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = NameNewSheet
    End If
End Sub
 Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet
     If wb Is Nothing Then Set wb = ThisWorkbook
     On Error Resume Next
     Set sht = wb.Sheets(shtName)
     On Error GoTo 0
     SheetExists = Not sht Is Nothing
 End Function

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Dec 5, 2016 at 15:29

Sgdva

2,8103 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

M.Shevade Over a year ago

Thank you! I will have to study this for sometime to understand it. I am completely new to programming, and this is my first task. Thank you for your help!

Tragamor · Accepted Answer · 2016-12-05 17:52:27Z

0

A generic set of functions to create non-duplicate sheets:

You can use the Cell.Values from column B as the strings to test

Sub Test()
    Call CreateNonDupeWS("Test1")
    Call CreateNonDupeWS("Test2", "Test1")
    Call CreateNonDupeWS("Test3", "Test1")
    Call CreateNonDupeWS("Test1")
End Sub

Private Function CreateNonDupeWS(wsNew As String, Optional wsAfter As String) As Boolean
On Error GoTo ExitSub
    If IsMissing(wsAfter) Then wsAfter = Sheets(Sheets.Count).Name
    If Not WorkSheetExists(wsNew) Then Worksheets.Add().Name = wsNew
    If WorkSheetExists(wsAfter) Then Worksheets(wsNew).Move After:=Worksheets(wsAfter)
    CreateNonDupeWS = True
ExitSub:
End Function

Function WorkSheetExists(ByVal sName As String) As Boolean
   On Error Resume Next
   WorkSheetExists = Not ActiveWorkbook.Worksheets(sName) Is Nothing
End Function

answered Dec 5, 2016 at 17:52

Tragamor

3,6893 gold badges18 silver badges33 bronze badges

1 Comment

M.Shevade Over a year ago

Thank you! I still don't understand a lot of it, and quite a few new commands to learn for me. I'll study it, and make it work hopefully! :)

Collectives™ on Stack Overflow

Excel: VBA create worksheets without duplicates

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related