Increasing the value of a cell by one per worksheet?

[futureruleroftheworld]

Hello all,

I'm new to excel programming.

I'm working with a large workbook (over 200 sheets) and I need to
input a number into the same cell (F1) in every sheet. I need sheet
001 to display "1" (no quotes) in F1, and sheet 002 to display "2",
003 to display 3, all the way to sheet 200 displaying '200'. What
formula can I use to achieve this output?

Another thing I need to do is come up with a function that takes a
number value in a cell in one sheet (one of the sheets named 001, 002,
003, etc.) and places it in a specific cell in, let's say, the "INDEX"
sheet. But I need the output cell in the "INDEX" sheet to be
different for every 001 002 003 004 sheet. Let's say I need the
number from cell A1 from every sheet, but I need each sheet to display
their respective A1's in a different cell in the INDEX sheet.

Yeah, it's confusing me. Thanks in advance.

 

[Gord Dibben]

For the first part use this macro.

Sub Number_Increment()
Dim myNum As Long
Dim iCtr As Long
myNum = 1
For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("F1")
.Value = myNum - 1 + iCtr
End With
Next iCtr
End Sub

For the second part..........

On the INDEX sheet enter the sheet names 001 through 200+ in Column A

Best way to do this is enter '001 in A1, '002 in A2..........note the
apostrophe.

Drag/copy down to A200

Then on INDEX sheet in B1 enter =INDIRECT(A1&"!"&"A1")

Copy that down 200+ rows.


Gord Dibben MS Excel MVP

 

[bigwheel]

Hello all,

I'm new to excel programming.

I'm working with a large workbook (over 200 sheets) and I need to
input a number into the same cell (F1) in every sheet. I need sheet
001 to display "1" (no quotes) in F1, and sheet 002 to display "2",
003 to display 3, all the way to sheet 200 displaying '200'. What
formula can I use to achieve this output?

Another thing I need to do is come up with a function that takes a
number value in a cell in one sheet (one of the sheets named 001, 002,
003, etc.) and places it in a specific cell in, let's say, the "INDEX"
sheet. But I need the output cell in the "INDEX" sheet to be
different for every 001 002 003 004 sheet. Let's say I need the
number from cell A1 from every sheet, but I need each sheet to display
their respective A1's in a different cell in the INDEX sheet.

Yeah, it's confusing me. Thanks in advance.

 

[Tom Ogilvy]

Sub ABC()
Dim i as long, s as String, sh as Worksheet
for i = 1 to 200
s = format(i,"000")
set sh = Worksheets(s)
sh.Range("F1").Value = i
worksheets("Index").Cells(i,1) = sh.Range("A1").Value
Next
End Sub

 

[bigwheel]

If you're talking about macros you could do this:-

For a = 1 To 200
Worksheets(a).Range("F1") = a
Next

for the first part of your request and

For a = 1 To 200
Cells(a + 1, 1) = Worksheets(a).Range("A1")
Next

should list the values in cell A1 for each sheet.

 

[Tom Ogilvy]

You should look closely at solutions that loop
for each sh in worksheet
or
for i = 1 to worksheets.count

type loops.

if you have a worksheet named Index, then if it is at the front, all the
numbers will be off by 1 and you will process the index sheet as well.

Just a thought.

I suppose you could do

for each sh in worksheets
if lcase(sh.name) <> "index" then
sh.Range("F1").Value = clng(sh.name)
end if
worksheets("Index").cells(clng(sh.name _
),"B").Value = sh.Range("A1").Value
next