Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

How to name excel sheets on values in columns [Solved/Closed]

Report
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
 -
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
 -
Hello, I have a list of people names in a column in one sheet with additional cells in each row for adding data.

I want to create separate excel sheet for each name in the column. I have 36 names in the list.

Please can anyone help me in this as I do not want to go on adding sheets and renaming them with the names in each cell.

Thanks,

Eskagsms

See more

5 replies

Answer 1 / 5
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 760
you can loop thru each name
create a sheet with that name

some thing like this 

   Dim lRow As Long
   Dim sSrcSheet As String
   
   sSrcSheet = "Sheet1" 'the name of the sheet that has the names
   lRow = 1
   Do While (Sheets(sSrcSheet).Cells(lRow, "A") <> vbNullString)
      Sheets.Add
      ActiveSheet.Name = Sheets(sSrcSheet).Cells(lRow, "A")
      lRow = lRow + 1
   Loop
Answer 2 / 5
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
Hello Riz,

I tried to paste the above in a module window and ran it, but it is not recognising the "Sheet1" parameter.

Please can you help.

Regards,

Eskagsms
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 760
do you have a sheet called "Sheet1" ?

Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , [http://www.editgrid.com etc
AND post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too.


Note: your data need not be to be real data but a good representative of how data looks like
Answer 3 / 5
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
Hello Riz,

I have uploaded a sample file at https://authentification.site/files/26805749/Sample.xls for your reference.

Please can you help.

Regards,

Eskagsms
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 760
I just took your file and used the code. It created all the sheets. What issue you are facing.
Answer 4 / 5
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
Hello Riz, please find attached the screen shot of the error for your reference.
Please can you advise the steps to use the code? I have uploaded the file at http://www.speedyshare.com/files/26819376/Macro_error_message.bmp

Regards,
Eskagsms
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 760
The code was just for how to name. You cannot add code like that. It has to be enclosed in a sub or function 

Public Sub doRenameSheet()
Dim lRow As Long
   Dim sSrcSheet As String
   
   sSrcSheet = "Sheet1" 'the name of the sheet that has the names
   lRow = 1
   Do While (Sheets(sSrcSheet).Cells(lRow, "A") <> vbNullString)
      Sheets.Add
      ActiveSheet.Name = Sheets(sSrcSheet).Cells(lRow, "A")
      lRow = lRow + 1
   Loop
End Sub
Answer 5 / 5
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
That worked fine. The problem is resolved. Thank you very much Riz.

Regards,

Eskagsms
Ask a question