CODEWITHSUNDEEP
excelvba
Stephen
Stephen

Reputation: 31

Increment Range Value by 1 in a loop

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.

Upvotes: 0

Views: 637

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Replace:

sheetname.Name = Worksheets("Update").Range("a2").Value

with:

sheetname.Name = Worksheets("Update").Range("a" & (r+1)).Value

So the first time through the loop we use a2, the next time we use a3, etc.

Upvotes: 2

Related Questions