Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-10-2013, 05:39 AM   #1
mahen
Newly Registered User
 
Join Date: Jan 2008
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
mahen is on a distinguished road
Work sheet names

How do I get all the worksheet names onto a worksheet without entering manually. See attahment.

Thanks.
~ Mahen
Attached Files
File Type: xls Master.xls (79.5 KB, 84 views)

mahen is offline   Reply With Quote
Old 01-11-2013, 01:00 AM   #2
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,701
Thanks: 39
Thanked 538 Times in 520 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
Re: Work sheet names

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
__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
Old 01-11-2013, 02:18 AM   #3
mahen
Newly Registered User
 
Join Date: Jan 2008
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
mahen is on a distinguished road
Re: Work sheet names

This is wonderful. Thank you....

~ Mahen

mahen is offline   Reply With Quote
Old 01-11-2013, 04:27 AM   #4
mahen
Newly Registered User
 
Join Date: Jan 2008
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
mahen is on a distinguished road
Re: Work sheet names

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.
mahen is offline   Reply With Quote
Old 01-11-2013, 06:00 AM   #5
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,701
Thanks: 39
Thanked 538 Times in 520 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
Re: Work sheet names

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
__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
Old 01-15-2013, 03:31 AM   #6
mahen
Newly Registered User
 
Join Date: Jan 2008
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
mahen is on a distinguished road
Re: Work sheet names

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
Attached Files
File Type: xls Master(1).xls (86.0 KB, 73 views)
mahen is offline   Reply With Quote
Old 01-15-2013, 05:09 AM   #7
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,701
Thanks: 39
Thanked 538 Times in 520 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
Re: Work sheet names

Add the line

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

Brian

__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
Old 01-15-2013, 06:13 AM   #8
mahen
Newly Registered User
 
Join Date: Jan 2008
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
mahen is on a distinguished road
Re: Work sheet names

Brian,
Working pefectly. How do I change the date (in B6) on all worksheets except names worksheet, without using link.

thanks.
mahen is offline   Reply With Quote
Old 01-15-2013, 06:26 AM   #9
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,701
Thanks: 39
Thanked 538 Times in 520 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
Re: Work sheet names

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
__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
Old 01-15-2013, 06:28 AM   #10
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,701
Thanks: 39
Thanked 538 Times in 520 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
Re: Work sheet names

BTW what do you mean by Link?

Brian
__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
Old 01-15-2013, 06:34 AM   #11
mahen
Newly Registered User
 
Join Date: Jan 2008
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
mahen is on a distinguished road
Re: Work sheet names

make a paste link and change on one. Rather have loop.
mahen is offline   Reply With Quote
Old 01-15-2013, 08:18 AM   #12
mahen
Newly Registered User
 
Join Date: Jan 2008
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
mahen is on a distinguished road
Re: Work sheet names

Changes made after grouping work perfect.
Thank you so much......
mahen is offline   Reply With Quote
Old 06-16-2018, 05:35 PM   #13
ahmed khater
Newly Registered User
 
Join Date: Jun 2018
Posts: 12
Thanks: 4
Thanked 0 Times in 0 Posts
ahmed khater is on a distinguished road
Re: Work sheet names

Thanks guys

ahmed khater is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Renaming Excel Sheet names from Access GovtWerker Modules & VBA 3 08-22-2012 01:16 AM
find last work sheet name smiler44 Excel 4 11-20-2010 09:19 AM
need help looking up sheet names joiemel Excel 7 08-14-2009 02:43 AM
Excel sheet names mahen Excel 18 01-29-2009 11:15 AM
get sheet names maxmangion Excel 2 05-28-2006 10:49 PM




All times are GMT -8. The time now is 03:54 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World