CODEWITHSUNDEEP
vbaexcel
DRUIDRUID
DRUIDRUID

Reputation: 369

Increment value on multiple worksheets

Have an excel worksheet that has several worksheets that are all labeled differently, no pattern at all (ie; Sheet1, Sheet2, Sheet3 etc) In the first worksheet on cell B2 I have a value of 90 and would like every other worksheet to auto increment value by 1. Would want second worksheet to have a value of 91 on B2 and so on,, I know i could tediously add =SUM('WorksheetName'!B2 +1) to do this but would take forever as i would have to manually type the sheet name each instance Any way to add some VBA? or another formula?

To help clarify, My first worksheet is labeled 101 and has a value of 90 on cell B2 enter image description here

Upvotes: 0

Views: 1075

Answers (2)

Sorceri
Sorceri

Reputation: 8033

Another example of how to accomplish the task. Added check for original worksheet

Sub SetWorksheetValues()
Dim ws As Worksheet
Dim firstWS As Worksheet
Dim cValue As Long
Dim counter As Long
'set the counter to 2
counter = 2
'set the first worksheet that has our beginning value
Set firstWS = ThisWorkbook.Worksheets("101")
'get the value and increment by 1
cValue = firstWS.Range("B1").Value + 1
    For Each ws In ThisWorkbook.Worksheets
        'skip the worksheet with our starting value
        If ws.Name <> firstWS.Name Then
            'set the cell value
            ws.Range("B" & counter).Value = cValue
            'increment each value by 1
            cValue = cValue + 1
            counter = counter + 1
        End If
    Next ws
End Sub

to test it

create a workbook and add a module in it. run the following macro

Sub AddLotsOfWorksheets()
Dim i As Long
Dim c As Long
Dim ws As Worksheet
c = 101
    For i = 1 To 250
        Set ws = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = CStr(c)
        c = c + 1
    Next i
End Sub

add 1 (or 90) in B1 on sheet 101

Run the SetWorksheetValues

sheet 350 cell B250 should have a value of 250 if 1 was entered or 339 if 90 was entered.

Upvotes: 1

SJR
SJR

Reputation: 23081

If I understand correctly, this should do what you want.

Sub x()

Dim i As Long, n As Long

n = 90

For i = 1 To Worksheets.Count
    Sheets(i).Range("B1").Offset(i - 1).Value = n + i - 1
Next i

End Sub

Upvotes: 0

Related Questions