Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Inputting worksheet tab name into column of cells

0

Hello,

I have a excel workbook that has +100 worksheets with different tab names. All of the names are dates but not in a consistent sequence. I would like to put every worksheet name into a column (let's say column A) on that specific worksheet. 

I found - Range("A2") = ActiveSheet.Name - to be useful as an input for only 1 row. Also, it is far from inputting for each worksheet seperately.

Can anyone help me out?

excelmacros-vba
posted by: furkanyildiz (rep: 4) on Fri Apr 18, 2025 at 8:26 am
Answer
Discuss

Answers

0
Selected Answer

Hello furkanyildiz and welcome to the forum,

There are a few ways to accomplish this. I have added these methods to your sample file and have attached it below.

If the sheet name is not yet in column "A", you can run a macro to bring all sheets up-to-date (sheet "16.04.2025"). Another option is to create a Worksheet_Change event. This is code which will run on a worksheet when specific cell(s) change ("WS Change Event" sheet in attached file). This method requires this event code be added to each worksheet. And a non-VBA way is to put a formula in each cell in column "A" to input the sheet name when column "B" contains data (sheet 17.04.2025"). There is also a simple non-VBA way – use a simple formula in column "A" (sheet "18.04.2025")

If this solves things for you please mark my Answer as Selected

Cheers   :-)

posted by: WillieD24 (rep: 687) on Fri Apr 18, 2025 at 3:09 pm
Discuss

Discussion

Hello WillieD24,  Thank you for the answer. It definitely solves my problem for 1 sheet. However, VBA WS Change Event - Sub Do_AllNames_Sheets() Does not work on all sheets for me. It only runs it for the active sheet. Do you think there is an error?
furkanyildiz (rep: 4) Apr 19, '25 at 2:09 am
furkanyildiz

You say that Willie's sokution doesn't work for all worksheets but did you follow his advice that "This method requires this event code be added to each worksheet"? 

You need to do that (or have a common module macro which is called by identical per sheet event procedures - and receives the sheet names if multiple sheets can change simultaneously). I'm away on a break but Willie can guide you. 

Please don't forget to mark his Answer as Selected when you've got it eirking correctly. 
John_Ru
John_Ru (rep: 6722) Apr 19, '25 at 4:08 am
Hello John_Ru,

I missed that. I got it working. Thank you both for the answers.
furkanyildiz (rep: 4) Apr 19, '25 at 7:17 am
@furkanyildiz - Glad you got it working.and thanks for selecting Willie's Answer. 

@Willie - well done! 
John_Ru (rep: 6722) Apr 19, '25 at 8:13 am
@furkanyildiz

Glad I was able to help and thanks for selecting my answer.
WillieD24 (rep: 687) Apr 19, '25 at 10:10 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login