Cell named after Tab name

[apompo21]

I'm trying to name in each cell in column I as long as there is data in the row after the tab name by using a macro that I started. For example, The tab name is Smith, Tom and rows 2 through 14 should say Smith, Tom in each cell in column I. This is my first post so I hope I attached the example file correctly.

[Logit]

.
This is one method :

Please Login or Register  to view this content.

[apompo21]

That didn't seem to work. Do I need to change Sheet1 in the formula to populate the cells with the sheet name?

[Marc L]

A VBA single codeline as a beginner starter : Sheet1.Range("I2:I" & Sheet1.[B1].End(xlDown).Row) = Sheet1.Name …

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !

[Logit]

That didn't seem to work. Do I need to change Sheet1 in the formula to populate the cells with the sheet name?

No .... so long as the index name of the sheet is SHEET1 ... the macro should work. Do you have more than one sheet this should be executed on ?

[apompo21]

Thanks for doing that. I tried it on a different tab and it doesn't seem to work for other tabs. I'm trying to figure out how to add the attachment.

[apompo21]

Yes, I have more than one sheet this should be executed on.

[Logit]

.
This edited macro will apply the same macro to all sheets in the workbook :

Please Login or Register  to view this content.

[Marc L]

Yes, I have more than one sheet this should be executed on.

Must be indicated in the initial post with an attachment accordingly !

Anyway, according to post #4, a beginner level VBA demonstration for all worksheets :

Sub Demo1() 
         Dim Ws As Worksheet 
    For Each Ws In ThisWorkbook.Worksheets 
             Ws.Range("I2:I" & Ws.[B1].End(xlDown).Row) = Ws.Name 
    Next 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[apompo21]

So, I just want to do one sheet at a time. How do I get it so it only does one sheet at a time? The first one doesn't seem to work. I'd like to be able to post the file that I'm using in here somehow but I can't figure it out. The second code works but it adds it to every single tab. Even tabs that don't need it. I don't know how to change it so it just does the tab that it's currently on.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Logit]

.
I realized an error I made in my last post.

The macro has been changed since then and currently works on all sheets in the workbook.

[Logit]

.
Update macro with userform :

Please Login or Register  to view this content.

See attached example.

[Marc L]

The second code works but it adds it to every single tab.

So obviously you just need the post #4 single codeline with the Activesheet statement as worksheet reference …

[apompo21]

Is there a way to do this without having to click the box to enter the names?

[apompo21]

Post 4 doesn't seem to work for some reason.

[Logit]

.
You'll need to perform some kinda action to make the macro fire. Don't believe it can get any easier.

Explain further what you are thinking ...

[apompo21]

Thanks everyone. I think I finally got it. I took one of the files someone sent and used portion of what they included.

[Logit]

.
Glad you have your answer.

Cheers and stay healthy.