Code for tab name from a cell of a different worksheet in same workbook

mckeamic

New Member
Joined
Nov 26, 2018
Messages
20
Good Evening,
I have been struggling with this macro code for a few days now and was hoping I can find the answers here.

I am a zookeeper (birds) and we are changing our Incubator room process to all digital (there is a spreadsheet we use for basic data "egg measurements, parameters for the machine like temperature & humidity, and pre-entered formulas to calculate weight loss...it all differs based on species) we then hand write on the sheet after it has been printed and it then goes into a binder....
Will try not to get too off track, but: I have a "DATA" sheet where most of my info will be entered and certain information will transfer to an individual sheet (each egg will have its own individual sheet)....

My question is: on the main worksheet "DATA" (where all my info is typed in) I need cell "B5" (which I will type in the egg log # it is assigned (i.e. B11-26-8) and I need it to transfer to the tab name of the individual egg sheet.

I found a code that allowed me to type (the egg log #) in cell "D7" of the actual individual egg worksheet and it would change the tab name at the bottom. (I also unfortunately lost that code and can't seem to find it through google. Which was somewhat helpful, but not exactly what I was looking for.

Any help would be much appreciated. Let me know if anyone has any questions in case I made this not very understandable, lol.
Thanks again
 
Did you have your sheets named

"A"

And "B"

You said sheet A and B

Those are odd names but that is what you said.
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sorry I am obviously very new to this and I've never done a chat forum before, I apologize for giving confusing information. But I do appreciate the help, its just hard to explain without visually showing.
Sheet A is actually labeled "DATA" and sheet B is labeled "1".
Since I have so much information that will auto populate data to each corresponding individual sheet.
I was just trying to get all my formulas correct, then just copy/paste to the new sheets (I will have over 500 sheets when all is said & done).
 
Upvote 0
Sorry I am obviously very new to this and I've never done a chat forum before, I apologize for giving confusing information. But I do appreciate the help, its just hard to explain without visually showing.

You may want to post some data sample in a table.

To copy a simple table from worksheet:
1. In your worksheet select the table/range
2. In the menu select border icon > select All Borders > then copy.
3. Back to the thread > in the reply box paste the table


A simple table would look like this:

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]NAME[/TD]
[TD="class: xl65, width: 64"]ID[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Mayson[/TD]
[TD="class: xl65, width: 64"]A01[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Amaya[/TD]
[TD="class: xl65, width: 64"]A03[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Cayden[/TD]
[TD="class: xl65, width: 64"]A04[/TD]
[/TR]
</tbody>[/TABLE]

OR you can upload a screenshot of your table to a website (for example: https://postimages.org/) then insert the image here or just put the link here.
 
Upvote 0
So, my questions:

1. when you insert data in B5 you want data in row 5 to be copied to sheets("1"), is it right? and when you insert data in B6 you want data in row 6 to be copied to sheets("2"), etc?
2. the data is from col A to what column?
3. do you also want to copy the header?
3. Do the target sheets (the sheets where you will paste the data, i.e sheets "1","2" etc) already exist or you want the macro to create it?
4. I suggest you use a button to run the macro instead of automatically run the macro after you type in column B.
 
Upvote 0
Ok (sorry was helping my daughter with her homework)...
Any information that is entered in column B for worksheet (A, titled "DATA") I would like for it to change the name of the corresponding tab name.
I.E. On worksheet "DATA" cell B5 needs to change the tab name of worksheet "1"
cell B6 needs to change the tab name of worksheet "2"
and so forth. I have about 600 tabs (one worksheet for each egg, in a given year we will have 200-500 eggs)

I will now try to answer your individual questions if the above didn't answer it.
1. when you insert data in B5 you want data in row 5 to be copied to sheets("1"), is it right? and when you insert data in B6 you want data in row 6 to be copied to sheets("2"), etc
* don't need the entire row, just need info from that particular cell B5 change the tab name of worksheet labeled "1"
2. the data is from col A to what column? don't need anything from column A
3. do you also want to copy the header? don't need header
3. Do the target sheets (the sheets where you will paste the data, i.e sheets "1","2" etc) already exist or you want the macro to create it? * I already have these sheets made, it just didn't show in the example I gave
4. I suggest you use a button to run the macro instead of automatically run the macro after you type in column B. *Not sure how to do that or not sure if I need it

Did that make sense? I'm really trying to explain what I need, I'm sorry if I'm not doing that correctly, but I do thank you for your help.
 
Upvote 0
Did that make sense? I'm really trying to explain what I need, I'm sorry if I'm not doing that correctly, but I do thank you for your help.

Ok, try this code:
Note: you should try it in a copy of your workbook, in case the result isn't what you expected.

Code:
Sub changeTab()

With Sheets("DATA")
va = .Range("B5", .Cells(.Rows.Count, "B").End(xlUp))
End With
    For i = 1 To UBound(va, 1)
    On Error Resume Next
    Sheets(CStr(i)).Name = Trim(va(i, 1))
    On Error GoTo 0
    Next

End Sub
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top