Excel Worksheets

[Guest]

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

Sub Number_Increment()
''increment a number in A1 across sheets
Dim mynum As Long
Dim ws As Long
mynum = 1
For ws = 1 To Worksheets.Count
With Worksheets(ws).Range("A1")
.Value = mynum - 1 + ws
'the -1 ensures first sheet A1 is 1
'change if want to start from another number
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

 

[Guest]

I do NOT understand your response. I am only familiar with Excel and its
functions. You seem to be alluding to some programmable subroutines which I
am NOT familiar with?

can you still be of assistance?

thanks,

FLKULCHAR

 

[Gord Dibben]

These are VBA macros and make life in Excel sooooooooooooo much easier.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your copied worksheet.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord

 

[Guest]

I am getting there!!!

What do you mean by, "paste the code in there"

What code??

thanks,

FLK

 

[Guest]

Believe it or not I have rum my first MACRO...a real simple one..."=789" in
cell B1..

now all I need to understand is the answer to my initial query..please
explain.thanks, FLKULCHAR

 

[Myrna Larson]

This "the code":

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

 

[Guest]

Well, I am extremely IMPRESSED; I did exactly what you wrote...ran the
macro...and it worked PERFECTLY..

Now, how will you explain to me the macro commands such as:

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

I DO NOT KNOW THIS PROGRAMMING LANGUAGE.

How can I learn it??

Thanks,

FLKULCHAR

 

[Guest]

What is the best method to learn the VGA programming language?

 

[Harlan Grove]

What is the best method to learn the VGA programming language?

This is like asking what's the best method to learn how to write prose. All
the VBA-specific books are at best like grammar texts with a few examples.
The really good programming books don't deal with VBA.

If you know any programming languages, you could get about as much useful
information from John Green's or John Walkenbach's Excel VBA books. If you
don't know any programming languages, you should start with a good general
programming text, probably one for Visual Basic. Search Amazon and read the
reader comments.