Work sheet names (1 Viewer)

mahen

Registered User.
Local time
Today, 18:59
Joined
Jan 13, 2008
Messages
56
How do I get all the worksheet names onto a worksheet without entering manually. See attahment.

Thanks.
~ Mahen
 

Attachments

  • Master.xls
    79.5 KB · Views: 186

Brianwarnock

Retired
Local time
Today, 18:59
Joined
Jun 2, 2003
Messages
12,701
You can do this with a macro

Code:
Sub sheetnames()
For i = 1 To Sheets.Count
   Sheets("names").Cells(i, 1).Value = Sheets(i).Name
Next i
End Sub

Brian
 

mahen

Registered User.
Local time
Today, 18:59
Joined
Jan 13, 2008
Messages
56
This is wonderful. Thank you....

~ Mahen
 

mahen

Registered User.
Local time
Today, 18:59
Joined
Jan 13, 2008
Messages
56
Brian,
Actullly I want the Names should be starting from C4(instead of A1) and numbers (1 2 3 .......) should come up from C5 please. This is to check how many names are there.

Thanks.
 

Brianwarnock

Retired
Local time
Today, 18:59
Joined
Jun 2, 2003
Messages
12,701
Ok it was a quick post without looking at your attachment to show the principle, to place it as per attachment.

Code:
Sub sheetnames()
Dim i As Long

For i = 1 To Sheets.Count
        
        Sheets("names").Cells(i + 3, "C").Value = Sheets(i).Name
        Sheets("names").Cells(i + 3, "B").Value = i
            
Next i

End Sub

Brian
 

mahen

Registered User.
Local time
Today, 18:59
Joined
Jan 13, 2008
Messages
56
Brian,
Working perfectly. What I wanted to do is put the value of E34 in each worksheet into names worksheet in column D in appropriate worksheet name. I have manually done with past link, but I want to do with macro if possible. See updated attached file.

Thanks

~ Mahen
 

Attachments

  • Master(1).xls
    86 KB · Views: 166

Brianwarnock

Retired
Local time
Today, 18:59
Joined
Jun 2, 2003
Messages
12,701
Add the line

Sheets("names").Cells(i+3,"D") = sheets(i).Range("e34")

Brian
 

mahen

Registered User.
Local time
Today, 18:59
Joined
Jan 13, 2008
Messages
56
Brian,
Working pefectly. How do I change the date (in B6) on all worksheets except names worksheet, without using link.

thanks.
 

Brianwarnock

Retired
Local time
Today, 18:59
Joined
Jun 2, 2003
Messages
12,701
click on Master tab
hold shift key whilst clicking on Tom
ie first and last sheets after names
this groups the sheets and what you do on one is done on all

to ungroup right click on Master and select ungroup from local menu.

Brian
 

mahen

Registered User.
Local time
Today, 18:59
Joined
Jan 13, 2008
Messages
56
make a paste link and change on one. Rather have loop.
 

mahen

Registered User.
Local time
Today, 18:59
Joined
Jan 13, 2008
Messages
56
Changes made after grouping work perfect.
Thank you so much......
 

Users who are viewing this thread

Top Bottom