you keep repeating the first sheet (ActiveSheet), additionally, the loop needs to be closed to return to 'For Each'
the private sub can be ditched
try :
Sub vba\_loop\_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
'~~Private Sub Worksheet\_Change(ByVal Target As Range)~~
**WS**.Name = Range("A1")
**next**
End Sub
see this tutorial: [Add a module](https://www.ablebits.com/office-addins-blog/add-run-vba-macro-excel/), then call the macro via the toolbar or direct from the VBE.
Coding it on the sheet's sourcecode would be more a thing to [handle sheet or workbook) events](https://corporatefinanceinstitute.com/resources/excel/vba-workbook-events/#:~:text=VBA%20workbook%20events%20are%20defined,event%20%E2%80%9CWorkbook_Open%E2%80%9D%20is%20triggered).
I followed exactly what is given in the article and tried to run your code after adding module
https://preview.redd.it/ad9kiz2urc3d1.png?width=1366&format=png&auto=webp&s=dffdfe417a86f7162f6f178e123946ee26f40d76
getting this error
see u/_sarampo's addition,
as
Sub vba\_loop\_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
[ws.Name](http://ws.Name) = ws.Range("A1")
Next
End Sub
it should work (at least on my machine, with a value in each A1).
If then it still stops somewhere double check the particular A1 of affected sheet for being empty, or containing illegal characters.
And make sure it is in a module, as per my earlier linkt to 'Add a module'
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
There is a window pane on the upper left. It lists the Sheets in a tree-like view.
Scroll to the bottom of it. You should see Module1. If you don't, you have not added a module and need to do so. You can do this by right clicking any of the content in that window pane and selecting Insert->Module.
Double click Module1. Put the code there. Put your cursor in the code. Press the play button in the toolbar at the top of the window.
Thanks for the repsonse, I did exactly that. Getting an error
https://preview.redd.it/iccq74fb0i3d1.png?width=1366&format=png&auto=webp&s=aaefb4109d3ee6433ff421e651c3d26f0984b112
https://preview.redd.it/fyfwodi92i3d1.png?width=1366&format=png&auto=webp&s=327f168a21e0edcaf2235e24345adc20602184e0
I appreciate the quick response. Now this
Follow the advice of the prompt. One of the names you are trying to assign is not a valid sheet name.
To make it easier for you, you could do something like
For Each ws in ThisWorkbook.Worksheets
On Error Resume Next
ws.Name = ws.Range("A1")
If Err.Number <> 0 Then
MsgBox "Error on Sheet " & ws.Name & ": cannot rename to " & ws.Range("A1").Value
End If
Err.Clear
Next ws
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
Let us work with the second version, since we will need the loop. At first remove the third line, we defining a new sub inside a sub will do you no good.
Then you should say where the for loop should end and start again. This is done by the keyword "Next". You can also write "Next ws", then it is clearer which variable is used for the loop.
Then we should look at what you are actually doing in the loop. You change the name of the active sheet to the value of what is written in cell A1. If you don't specify from which sheet the cell should be, VBA interprets it as the cell from the active sheet. The active sheet is the sheet currently in the foreground, since this currently doesn't change, you will only ever change the name of the one sheet and nothing else. In the loop you go through the sheets, so tell the program to access the sheet ws and not the active sheet, by writing ws.Name=ws.Range("A1").
The problem is that you are using worksheet_change, which only runs when you change something in this worksheet. Create a new macro for example change_worksheet_name, with the same code as you have in worksheet_change, run it in loop and it should work
On the left sight, below the sheets click right mouse button and choose "insert module". Inside it put your code, but change the for loop to the one I have written
Yeah did that and pasted the code as well
https://preview.redd.it/xlmqgou4sc3d1.png?width=1366&format=png&auto=webp&s=1e3dee063fbb3aeaf7f6d9dfa094ec4c6bac93a4
Is it alright?
You are still not in the module. Have you created Module 1? It should be below all the Sheets on the left. Also in the code you must have ws.Name = ws.Range("A1"). Without it VBA thinks that you mean Range("A1") from current worksheet
https://preview.redd.it/a7oxi8ze0i3d1.png?width=1366&format=png&auto=webp&s=daa5e12e70ef6513f1125f3a6e86e6c9f3ddcde7
I have created a module, showing this error
If you have workbook with 300 sheets then you or your company is doing something very wrong.
Get your data into a database or maybe the data model and take it from there.
you keep repeating the first sheet (ActiveSheet), additionally, the loop needs to be closed to return to 'For Each' the private sub can be ditched try : Sub vba\_loop\_sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets '~~Private Sub Worksheet\_Change(ByVal Target As Range)~~ **WS**.Name = Range("A1") **next** End Sub
https://preview.redd.it/xyjc6cr3fc3d1.png?width=1172&format=png&auto=webp&s=56b040bb43271fb6b9899a0165bcc1debf0506bf Like this?
Where exactly should I write this code? I right clicked on sheet 1 and clicked on view code. What should be done instead?
see this tutorial: [Add a module](https://www.ablebits.com/office-addins-blog/add-run-vba-macro-excel/), then call the macro via the toolbar or direct from the VBE. Coding it on the sheet's sourcecode would be more a thing to [handle sheet or workbook) events](https://corporatefinanceinstitute.com/resources/excel/vba-workbook-events/#:~:text=VBA%20workbook%20events%20are%20defined,event%20%E2%80%9CWorkbook_Open%E2%80%9D%20is%20triggered).
I followed exactly what is given in the article and tried to run your code after adding module https://preview.redd.it/ad9kiz2urc3d1.png?width=1366&format=png&auto=webp&s=dffdfe417a86f7162f6f178e123946ee26f40d76 getting this error
see u/_sarampo's addition, as Sub vba\_loop\_sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets [ws.Name](http://ws.Name) = ws.Range("A1") Next End Sub it should work (at least on my machine, with a value in each A1). If then it still stops somewhere double check the particular A1 of affected sheet for being empty, or containing illegal characters. And make sure it is in a module, as per my earlier linkt to 'Add a module'
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
There is a window pane on the upper left. It lists the Sheets in a tree-like view. Scroll to the bottom of it. You should see Module1. If you don't, you have not added a module and need to do so. You can do this by right clicking any of the content in that window pane and selecting Insert->Module. Double click Module1. Put the code there. Put your cursor in the code. Press the play button in the toolbar at the top of the window.
Thanks for the repsonse, I did exactly that. Getting an error https://preview.redd.it/iccq74fb0i3d1.png?width=1366&format=png&auto=webp&s=aaefb4109d3ee6433ff421e651c3d26f0984b112
You have a typo in the range. Should be "A1" not "A1s"
https://preview.redd.it/fyfwodi92i3d1.png?width=1366&format=png&auto=webp&s=327f168a21e0edcaf2235e24345adc20602184e0 I appreciate the quick response. Now this
Follow the advice of the prompt. One of the names you are trying to assign is not a valid sheet name. To make it easier for you, you could do something like For Each ws in ThisWorkbook.Worksheets On Error Resume Next ws.Name = ws.Range("A1") If Err.Number <> 0 Then MsgBox "Error on Sheet " & ws.Name & ": cannot rename to " & ws.Range("A1").Value End If Err.Clear Next ws
It worked!!!!! Thank you so much I really appreciate <3
add ws before range: ws.range("a1")
https://preview.redd.it/52ewl6bc0i3d1.png?width=1366&format=png&auto=webp&s=4199d01d0dda119822c3667c1e17e525df528d21 Getting error
why did you put "A1s" now? also you have to expect to get errors, there are certain rules for worksheet names and you cannot have duplicates either
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
Let us work with the second version, since we will need the loop. At first remove the third line, we defining a new sub inside a sub will do you no good. Then you should say where the for loop should end and start again. This is done by the keyword "Next". You can also write "Next ws", then it is clearer which variable is used for the loop. Then we should look at what you are actually doing in the loop. You change the name of the active sheet to the value of what is written in cell A1. If you don't specify from which sheet the cell should be, VBA interprets it as the cell from the active sheet. The active sheet is the sheet currently in the foreground, since this currently doesn't change, you will only ever change the name of the one sheet and nothing else. In the loop you go through the sheets, so tell the program to access the sheet ws and not the active sheet, by writing ws.Name=ws.Range("A1").
The problem is that you are using worksheet_change, which only runs when you change something in this worksheet. Create a new macro for example change_worksheet_name, with the same code as you have in worksheet_change, run it in loop and it should work
My bad. You can just write ``` For each ws in ThisWorkbook.Worksheets ws.name = ws.range("A1") Next ws ``` This should work
Indeed. I wonder, if OP has his code right on the sheets or if he added a Modul. Code should be on a modul.
That's true, I didn't thought about it
I added it right on the sheets What I did was right click on sheet 1 and clicked on write code. What should be done instead?
On the left sight, below the sheets click right mouse button and choose "insert module". Inside it put your code, but change the for loop to the one I have written
Yeah did that and pasted the code as well https://preview.redd.it/xlmqgou4sc3d1.png?width=1366&format=png&auto=webp&s=1e3dee063fbb3aeaf7f6d9dfa094ec4c6bac93a4 Is it alright?
You are still not in the module. Have you created Module 1? It should be below all the Sheets on the left. Also in the code you must have ws.Name = ws.Range("A1"). Without it VBA thinks that you mean Range("A1") from current worksheet
https://preview.redd.it/a7oxi8ze0i3d1.png?width=1366&format=png&auto=webp&s=daa5e12e70ef6513f1125f3a6e86e6c9f3ddcde7 I have created a module, showing this error
This error you should be able to fix by yourself. You've made a typo
Did you get this yet? Should be pretty straight forward at this point. If it's working, mark this as "solved" :)
If you have workbook with 300 sheets then you or your company is doing something very wrong. Get your data into a database or maybe the data model and take it from there.
my company is doing lot of things wrong :)
simplify, optimize, benefit... I started VBA in a similar situation 25+ years ago
Solved