how to use VBA to name sheets ?

  • Thread starter Thread starter vumian
  • Start date Start date
V

vumian

I have a file that i do not know how many sheet are there in before

i need to copy contain of C4 to name of sheets

Thank for your help
 
Sub Renamesheets()
dim sh as Worksheet
for each sh in workbook.Worksheets
sh.name = sh.Range("C4").Value
Next
end Sub


would be my guess of what you want
 
If you want to add the value in C4 from each sheet to the existing sheet
name you could do like this:

Sub Change_name()

Dim i As Integer

For i = Sheets.Count To 1 Step -1

Application.ScreenUpdating = False

Sheets(i).Activate

Sheets(i).Name = Sheets(i).Name & Range("C4").Value

Next i

Application.ScreenUpdating = True

End Sub
 
or to make it faster

Sub Change_name()
Dim sh as Worksheet
For each sh in Worksheets
sh.Name = sh.Name & sh.Range("C4").Value
Next i
End Sub

Almost always better to avoid selecting and activating which are usually not
necessary
 
Small typo in your macro Tom

Line "Next i" should be "Next sh"
But your code is more efficent.

I tried first:

Sheets(i).Name = Sheets(i).Name & Range("C4").Value
But that only added the "C4" value from the sheet that is active when a run
the macro.

Your "sh.Name = sh.name & sh.Range("C4").Value" is a verry nice solution
making activating
the sheet not nececearry. I bow to your superior knowledge.
 
thanks for the correction - I was editing your macro and didn't get the NEXT
cleaned up after I switched the loop to a For Each.
 
Back
Top