Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
"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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Search multiple worksheets - Excel 97 | Excel Discussion (Misc queries) | |||
Appending excel worksheets | Excel Worksheet Functions | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
How to send Excel 2000 worksheets to Excel 2003? | Excel Discussion (Misc queries) |