Naming sheet from cell in a different sheet

[Corryn99]

Hi,

I'm creating a template to keep track of student progress, and I want to create a macro to automatically name each sheet based on values input into an overview sheet.

I started with code I found from ExtendOffice: How to name sheets based on cell values, and updated the code to this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Worksheets("Overview").Range("A5")
    If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub

I'm not sure why but I cannot get this code to work. If I take out the Worksheets("Overview"), and assign the range to a cell within the sheet I'm trying to name, it works fine.

Any help would be appreciated!

 

[Joe4]

Welcome to the Board!

If event procedures, like Worksheet_Selection, you typically do NOT set the "Target" range - it is automatic. It is the cell that was selected that triggered the code to run automatically.
Note that the way you have written it, it would call this to run anytime any cell is selected on your sheet. I don't think that is what you intended.
It also has other issues with it.

Let's take a step back. Can you tell us exactly how you want this to work?
At what point should it rename the sheet?
And obviously, I don't think every sheet will be looking at the same cell (A5), or else it would try to give them all the same name (and fail).

Please explain to us, in plain English, exactly how you envision this working.