Creating sheet with list having duplicate values.

Closed
sandesh_dbz Posts 3 Registration date Thursday October 22, 2015 Status Member Last seen October 23, 2018 - Oct 22, 2015 at 09:44 AM
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 - Oct 22, 2015 at 11:21 AM
Hello,
I have a list in a column need to create sheets with each value in the list. The list will have duplicate values. but excel cannot have two sheet with same name so the next sheet should have 1,2,3 number as per the record appear in the list.
Fro eg. list
Apple
Bat
Cat
Dog
Apple
Cat
Apple
So the above list has apple and cat value more than one times so the first sheet name should be Apple and next should be Apple(2) and next Apple(3)

2 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Oct 22, 2015 at 11:07 AM
could you share what you have done so far
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Oct 22, 2015 at 11:21 AM
Hi Sandesh_dbz,

Try the following code:
Sub RunMe()
Dim lRow As Integer

lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A2:A" & lRow)
    If WorksheetExists(cell.Value) = True Then
        Sheets(cell.Value).Copy after:=Sheets(Sheets.Count)
    Else
        Sheets.Add after:=Sheets(Sheets.Count)
        ActiveSheet.Name = cell.Value
    End If
Next cell

End Sub

Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean

On Error Resume Next
WorksheetExists = (Sheets(WorksheetName).Name <> vbNullString)
On Error GoTo 0

End Function


How to implement and run a code:

- From Excel hit Alt + F11 to open the "Microsoft Visual Basic" window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro's.
- Double-click the macro you wish to run.
NOTE: macro's cannot be reversed using the blue arrows. Always make sure you save your file before running a code, so you can reopen your file if something unforeseen happens or you want to go back to the situation before the code was run.

Best regards,
Trowa
0