Same Increment in Multiple Worksheets

[Guest]

Question



How do you get a value within a cell to increment, by say, 1, for each and
every worksheet within my workbook?

For example, the value in sheet1 in cell A1 is 100

in sheet2, I input...= 1 + 'sheet1'!A1 in cell A1 (value now is 101)

then, in sheet 3, I input...= 1 + 'sheet2'!A1 in cell A1 (value now is 102)

then, in sheet 4, I input...= 1 + 'sheet3'!A1 in cell A1 (value now is 103)


etc.,etc.,etc.

How can I input a formula...then copy and paste it into my sheets without
having to go to each and every worksheet to input the formula??

Thank you,

FLKulchar

 

[Gord Dibben]

FLKulchar

As earlier but with a couple of alterations. Mainly you wanted to start at
100 on first sheet.

Sub Number_Increment()
''increment a number in A1 across sheets
Dim mynum As Long
Dim ws As Long
mynum = 100 'your choice of start number
For ws = 1 To Worksheets.Count
With Worksheets(ws).Range("A1")
.Value = mynum - 1 + ws
'the -1 ensures first sheet A1 is 100
End With
Next ws
End Sub

Alternative if you want to have a formula in each A1 referring to the sheet
before as per your example.

Sub Fill_Across_Sheets()
Dim sh As Worksheet
Dim i As Integer
Dim ShName As String

For i = 2 To Worksheets.Count
ShName = Worksheets(i - 1).Name
Worksheets(i).Range("A1").Formula = "='" & ShName & "'!A1+1"
Next
End Sub


Gord Dibben Excel MVP

 

[Ragdyer]

This will work *only* when you're using the XL default sheet names (Sheet1,
Sheet2, ... etc.).
Also, the WB has to be "Saved" at least once.

This will give you the value of A1 in Sheet1 incremented to 1 *less* then
the sheet name.

On Sheet1, in A1, you enter any starting number, i.e. 100.

Now *group* all your other sheets together, so that whatever you do to one,
will be duplicated in all the others.

Click in the tab of Sheet2, hold <Shift>, and click in the tab of your last
sheet tab.

All the tabs are now white, with the tab of Sheet2 in bold, denoting that
Sheet2 is the main sheet in focus.
You'll also see "[Group]" appended to the WB name in the Title bar.

Now, click in A1 (you should already be in Sheet2), and enter (paste) this
formula:

=Sheet1!A1+RIGHT(CELL("Filename",A1),LEN(CELL("Filename",A1))-FIND("]",
CELL("Filename", A1))-5)-1

Click in the tab of Sheet1 to break out of the grouping, and you should now
have what you're looking for.