Previous Topic Next Topic

How to name excel sheets on values in columns

Solved/Closed
eskagsms
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
 - Feb 9, 2011 at 02:47 AM
eskagsms
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
 - Feb 14, 2011 at 01:01 AM
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

5 replies

Answer 1 / 5
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
Feb 9, 2011 at 06:38 AM
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
0
Answer 2 / 5
eskagsms
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
Feb 11, 2011 at 01:46 AM
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
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
Feb 11, 2011 at 06:15 AM
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
0
Answer 3 / 5
eskagsms
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
Feb 11, 2011 at 06:52 AM
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
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
Feb 11, 2011 at 10:56 AM
I just took your file and used the code. It created all the sheets. What issue you are facing.
0
Answer 4 / 5
eskagsms
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
Feb 12, 2011 at 12:07 AM
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
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
Feb 12, 2011 at 09:13 AM
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
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 5
eskagsms
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
Feb 14, 2011 at 01:01 AM
That worked fine. The problem is resolved. Thank you very much Riz.

Regards,

Eskagsms
0