Solved: Create New Sheet Based on Value In Column

[U_Shrestha]

Hi,

I have a workbook that constantly requires new sheets to be added. Therefore, I want to set the Sheet1 as the page that lists the names of each sheets and the description of what the sheet contains.

My question is, is it possible to insert a new sheet whenever I put a name in column A? Practically, I will set up column A as the column that I will use to list the name of the sheets in the workbook, so if I type "Jamie" in A3, is it possible to create a new sheet named as "Jamie"? (The Table heading is at Row 2, the sheet I want to list all the names of the sheets is "Menu") Another thing is, is it possible to arrange all the sheets, except "Menu" sheet to be arranged in alphabetical order, I need to have the sheet "Menu" to appear first.

I would appreciate any help. Thanks.

[JimmyTheHand]

Try this.

Jimmy

[marshybid]

Hi Jimmy,

I like the code and example you sent for this thread.

Is there an easy way of adapting the code to do the following;

Incorporate as part of an existing macro, using column B, create 2 worksheets for each unique value found (Heidl Ltd, Benz Ltd, Williams & Co, etc) there will be multiple rows for each value. The worksheets will need to be Named as follows;

Heidl
Heidl Pivot
Benz
Benz Pivot
Williams
Williams Pivot

So I only want to use the first word in each unique value to name the first worksheet then the same + the word Pivot to name the second worksheet.

Also, once these have been created I need to refer to the worksheets as follows

For each ws (that does not contain "pivot")

Copy (a range) from source data and paste

then when creating pivot tables I need to be able to say for for each ws (that does not contain pivot) SourceData = ws name, TableDestination = ws name & Pivot

I hope the above makes sense, any help greatly appreciated.

Marshybid

[U_Shrestha]

This is magic!! Thank you soooooo much Jimmy! It is just what I needed. I appreciate your help very much :)

[JimmyTheHand]

U_Shrestha, you are welcome :yes

Marshybid, below is the code for creating worksheets. You can copy this sub (and the accompanying functions) into your project and call it when necessary. Only need to supply it with the range that contains the unique names. Note: unlike the version I posted earlier, this one doesn't delete sheets whose names are not in the list.

Sub CheckAndCreateSheets(rngNames As Range)
Dim cel As Range, WS As Worksheet, Answ

For Each cel In rngNames
If NameCount(rngNames, cel.Value) > 1 Then
Answ = MsgBox("Duplicate name found: " & cel & vbLf & "Remove? (Click 'No' to rename)", vbYesNo)
If Answ = vbYes Then
cel.ClearContents
Else
Do
Answ = InputBox("Enter a unique name!")
Loop Until NameCount(rngNames, Answ) = 1
cel.Value = Answ
End If
End If
Next
rngNames.Sort key1:=rngNames(1), order1:=xlAscending

For Each cel In rngNames
If cel <> "" Then
If Not WorksheetExist(cel.Value, WS) Then
Set WS = Worksheets.Add
WS.Name = cel.Value
End If
If Not WorksheetExist(cel.Value & " Pivot", WS) Then
Set WS = Worksheets.Add
WS.Name = cel.Value & " Pivot"
End If
End If
Next
End Sub

Private Function WorksheetExist(Name As String, WS As Worksheet) As Boolean
On Error GoTo EH
Set WS = Sheets(Name)
WorksheetExist = True
EH:
End Function

Private Function NameCount(Rng As Range, Name) As Long
NameCount = Application.WorksheetFunction.CountIf(Rng, Name)
End Function


And here is the loop for non-pivot sheets.

Sub IterateNonPivotSheets()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Sheets
If InStr(WS.Name, "Pivot") = 0 Then
'your code
End If
Next
End Sub
Unfortunately the rest is too confusing for me to say any useful.

Jimmy

[marshybid]

Hi Jimmy,

thnaks for your help.

I will have a look at putting it into practice tinight.

Marshybid :hi:

[U_Shrestha]

Hello Jimmy,

I have two questions:

1) I also want to add a hyperlink to the text I add in column A, so when I type e.g."January" in column A, then your current code creates a Sheet Named "January", and then also sorts all the texts in column A. Is it possible to add a hyperlink to the text "January" automatically, so that when I type "January" it will also add hyperlink to cell A1 of the sheet "January"? What does "Munka1" mean in your sample sheet?

2)What is the best way to use your code in a workbook that already has about 20 tabs? In my current workbook with about 20 worksheets, I named first sheet as "Menu" and manually listed the names of each tabs; the table heading is in Row 2, then I copied your code as worksheet module, and then added a new worksheet named "FamilyInfo" but it gave me error message and highlighted following line, and also sorted the table headline alphabetically. The table headline is at Row 2, how can I avoid the sorting of table headline?

WS.Name = cel.Value



Thanks again.

[U_Shrestha]

Addendum to item 2)

I had created a data list for the sheet names, when I converted the data list in "Menu" to range, the table heading doesn't get sorted alphabetically anymore.