Copy sheet and rename from a list, ignore duplicates

bwkisley · Nov 3rd 2020

Hello everyone! After days of trying to figure this out on my own and searching for help online, I finally found what I needed, but the moderator closed the thread on me so I need to create my own. The below link will take you to this thread.

I need a macro that copies the "Blank Client" tab and creates a new tab for each unique value starting in cell A2 and going down on the "Data Validation" tab. Each newly created tab should be renamed to whatever is in cell A2, cell A3, cell A4......etc on this "Data Validation" tab. If a tab already exists with a value in column A, this Macro needs to skip/ignore it and do nothing. All new sheets should be added after my "Project Tracker" tab (ideally) otherwise at the end. Ideally, this would alphabetize the tabs as well starting after my "Client Tracker" tab so each name in cells A2:A??? on the "Data Validation" tab would be in ABC order.

Hopefully this all makes sense and thank you so much in advance for any support you can provide!!

**royUK** · Nov 3rd 2020

Quote

Hello everyone! After days of trying to figure this out on my own and searching for help online, I finally found what I needed, but the moderator closed the thread on me so I need to create my own.

That's the Forum Rules, try reading them.

Attach an example workbook.

bwkisley · Nov 3rd 2020

By me saying "moderator closed the thread on me so I need to create my own" I didn't mean that as being upset, sorry if you read it that way. I did exactly what another person did in that thread and I had no problem setting a new thread on my own. I needed to provide some back story so someone wouldn't just say "this has been resolved already see here (the thread I posted).

Also you didn't attach anything if you were trying to help

**royUK** · Nov 3rd 2020

I want you to attach an example workbook

bwkisley · Nov 3rd 2020

Attached is a dumbed down version of my file. Command button will go on the Project Tracker tabExample File.xlsm

**royUK** · Nov 3rd 2020

Which list would you use?

I would actually, just have the same set up but not copy sheets. Populate the sheet from a database of projects. If yo keep adding sheets you will find it difficult to manage

bwkisley · Nov 3rd 2020

The user would fill out the "Project Tracker" data table. As part of a macro I will then refresh the pivot table on the "Data Validation" tab which will pull all unique names. This was my way around avoiding duplicate sheets for the same person since I was struggling figuring that out. So the list would start from cell A2 on the Data Validation tab and go down from there for each new unique name

**royUK** · Nov 3rd 2020

I would actually, just have the same set up but not copy sheets. Populate the sheet from a database of projects. If yo keep adding sheets you will find it difficult to manage

I would use VLOOKUP to populate or probably INDEX/MATCH to populate the form sheet. XLOOKUP if you are using MICROSOFT 365

bwkisley · Nov 3rd 2020

I unfortunately can not visualize what you are recommending. I am very comfortable with Vlookups - ok with index/match - but not sure how that would allow me to be able to see the Project details for each client. Are you saying like a drop down with all client names that the user picks then a vlookup to pull that data relative to that client? "Populate the sheet from a database of projects" is where I am getting a bit hung up

**royUK** · Nov 3rd 2020

You need to create a sheet that contains all the project information. I'm not sure if that would be your project tracker

bwkisley · Nov 3rd 2020

My concern with this though is each client could have multiple projects. So having each client have their own sheet where the user can populate each project detail I think would be best still. Otherwise with using Vlookups and Index/Match you might not capture every single project description. I already have a macro in my full version that auto-populates a hyperlink to the correct sheet which will go in the "Jump to" column on the project tracker tab. And on Each tab a hyperlink to take them back to the Project Tracker. So it will be very user friendly and easy to switch back and forth.

**royUK** · Nov 3rd 2020

I can't see how you can avoid duplicate sheets. You would need to check before creating the new sheet. Also, how would you code kow which client to creae for?

bwkisley · Nov 3rd 2020

That's exactly what the code in the other thread does. It looks at a range of data, finds the unique values that are not currently a sheet name and then creates a new sheet with that unique value as a name. If the value is already a sheet name, it skips it. Each new sheet is a copy of a "master" blank template sheet aka my "Blank Client" sheet.

**royUK** · Nov 3rd 2020

This should do what you want. It loops through your list and adds new sheets i required

Code

Option Explicit




Sub CreateNewClientSheet()
Dim rRng As Range, rCl As Range
Dim sNm As String
With Worksheets("Data Validation")
Set rRng = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With
For Each rCl In rRng.Cells
sNm = rCl.Value
''///add new sheet (only if required-NB uses UDF)
If Not WksExists(sNm) Then
Worksheets("Blank Client").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = sNm
ActiveSheet.Cells(5, 5).Value = sNm
End If
Next rCl
End Sub
''/// used to check if a specific sheet exists
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

Display More

bwkisley · Nov 3rd 2020

You Sir are a GENIUS! Now I will just need to add in my create hyperlink for each client code and maybe look into finding out how to alphabetize these created tabs and it will be perfect! Thank you so much for your help!

**royUK** · Nov 4th 2020

Do you mean to sort the sheets?

bwkisley · Nov 4th 2020

Sorry, yes sorry I meant sort in alphabetical order those created sheets. It is not crucial, just a would be nice kind of thing. I'm extremely happy with your code as it is so thank you again!

**royUK** · Nov 4th 2020

This will sort sheets, run it after you have added the new sheets

Code

Sub SortSheets()
Dim I As Integer, J As Integer


For I = 1 To Sheets.Count - 1
For J = I + 1 To Sheets.Count
If UCase(Sheets(I).Name) > UCase(Sheets(J).Name) Then
Sheets(J).Move Before:=Sheets(I)
End If
Next J
Next I
End Sub

Display More

bwkisley · Nov 5th 2020

Thank you Roy! This file is working exactly as I hoped! I very much appreciate all you have done!

**royUK** · Nov 5th 2020

Pleased to help

Copy sheet and rename from a list, ignore duplicates

Participate now!

Share

Tags

Users Viewing This Thread