My goal is to rename many worksheets from a worksheet that contains stock market codes. To do this I have the codes in a worksheet called Update. The codes are from A2 to A10. I have set up a For loop to goto the next activeworksheet and as the range value is increased, the worksheet gets renamed to the new cell value in the Update WSheet
The problem I have is that I want the Range value to increase by 1 which will select the next name for the worksheet. I have tried adding 1 to the Range value but did not work
Sub changeWSnames()
Dim sheetname As Worksheet
Dim r As Integer
For r = 1 To 10
ActiveWorkbook.Worksheets(r).Activate
Set sheetname = ActiveWorkbook.ActiveSheet
sheetname.Name = Worksheets("Update").Range("a2").Value
Next r
r = r + 1
End Sub
What I need is too workout is how to increment the .Range("a2").Value
, i.e. to increase by 1
, example it becomes Range("a3").Value
etc etc.
.Activate
instead reference your sheet directly:Set sheetname = ActiveWorkbook.Worksheets(r)
and you don't need ther = r + 1
line.