Add worksheets and rename

[eriol]

Hello everyone. I have a following problem...

I need to add multiple worksheets in the workbook using one sheet as a template. I googled a bit and have found this macro:

Please Login or Register  to view this content.

It works great. It uses worksheet "form" as template and gives names to new worksheets form the column A on the sheet "input"

However, when I got the real data I'm gonna use I found out that there will be items with the same name in A1 on the worksheet "Input'.

For example, in the column A1 I will have values: Pencil, Desk, Binder, Pencil. And in the column C, I will have colors : red, blue, green

So, I need to create 4 worksheets with names : Pencil, Desk, Binder, Pencil (1) or with the names :Pencil (Blue), Desk, Binder, Pencil (Red) if it is [possible to use data from 2 columns for worksheets names.

I hope I am clear enough. I guess the solution is not very complicate but I am a beginner in VBA and can not solve it.

Thanx in advance, Eriol

[Richard Buttrey]

Hi,

Why not concatenate columns A & C in say column D1 and copied down. e.g. =A1&" ("&C1&")", then change the line in your macro to


Set rngName = ThisWorkbook.Sheets("Input").Range("D1")

[eriol]

Thanx for your answer!

I can't do that because I use data in A1 column as reference for lookup(on the sheet "form" there is a user-form with several rows that uses lookup to retrieve data from "input" sheet).

However you gave me the idea to use data for some other column to retrieve data with "index/match" formula. However I still have to find a way to rename sheets.

Thanx again