Excel Worksheets
 

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

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

On Thu, 29 Sep 2005 16:03:01 -0700, FLKULCHAR
wrote:

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

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" wrote:

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

On Thu, 29 Sep 2005 16:03:01 -0700, FLKULCHAR
wrote:

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

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 InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your copied worksheet.

Run the macro by going to ToolMacroMacros.

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


Gord

On Fri, 30 Sep 2005 15:36:03 -0700, FLKULCHAR
wrote:

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" wrote:

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

On Thu, 29 Sep 2005 16:03:01 -0700, FLKULCHAR
wrote:

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

I am getting there!!!

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

What code??

thanks,

FLK

"Gord Dibben" wrote:

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 InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your copied worksheet.

Run the macro by going to ToolMacroMacros.

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


Gord

On Fri, 30 Sep 2005 15:36:03 -0700, FLKULCHAR
wrote:

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" wrote:

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

On Thu, 29 Sep 2005 16:03:01 -0700, FLKULCHAR
wrote:

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

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

"FLKULCHAR" wrote:

I am getting there!!!

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

What code??

thanks,

FLK

"Gord Dibben" wrote:

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 InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your copied worksheet.

Run the macro by going to ToolMacroMacros.

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


Gord

On Fri, 30 Sep 2005 15:36:03 -0700, FLKULCHAR
wrote:

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" wrote:

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

On Thu, 29 Sep 2005 16:03:01 -0700, FLKULCHAR
wrote:

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

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

On Sat, 1 Oct 2005 16:05:01 -0700, FLKULCHAR
wrote:

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

"FLKULCHAR" wrote:

I am getting there!!!

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

What code??

thanks,

FLK

"Gord Dibben" wrote:

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 InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your copied worksheet.

Run the macro by going to ToolMacroMacros.

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


Gord

On Fri, 30 Sep 2005 15:36:03 -0700, FLKULCHAR
wrote:

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" wrote:

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

On Thu, 29 Sep 2005 16:03:01 -0700, FLKULCHAR
wrote:

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

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




"Myrna Larson" wrote:

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

On Sat, 1 Oct 2005 16:05:01 -0700, FLKULCHAR
wrote:

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

"FLKULCHAR" wrote:

I am getting there!!!

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

What code??

thanks,

FLK

"Gord Dibben" wrote:

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 InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your copied worksheet.

Run the macro by going to ToolMacroMacros.

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


Gord

On Fri, 30 Sep 2005 15:36:03 -0700, FLKULCHAR
wrote:

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" wrote:

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

On Thu, 29 Sep 2005 16:03:01 -0700, FLKULCHAR
wrote:

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

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

"FLKULCHAR" wrote:

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




"Myrna Larson" wrote:

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

On Sat, 1 Oct 2005 16:05:01 -0700, FLKULCHAR
wrote:

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

"FLKULCHAR" wrote:

I am getting there!!!

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

What code??

thanks,

FLK

"Gord Dibben" wrote:

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 InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your copied worksheet.

Run the macro by going to ToolMacroMacros.

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


Gord

On Fri, 30 Sep 2005 15:36:03 -0700, FLKULCHAR
wrote:

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" wrote:

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

On Thu, 29 Sep 2005 16:03:01 -0700, FLKULCHAR
wrote:

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

"FLKULCHAR" wrote...
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.