Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering Sheets incrementally (as in Invoice numbers)
With the least amount of keystrokes, how can I have a number that is typed in
cell J5 on 32 sheets in a workbook increase AUTOMATICALLY by one. On the first sheet, I type 1000 in cell J5. Automatically then I want cell J5 on sheet 2 to say 1001, and so on up to 1032. PLEASE GIVE ME EVERY STEP, since I have never entered a macro before,ie, I don't know exactly where to enter it, how to save it, how to run it, etc. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering Sheets incrementally (as in Invoice numbers)
Steve,
This macro will do what you want. Note that you need to set the name of the first sheet on line where the '<<<<< is. '------------------copy start Option Explicit Sub doformula32sheets() Dim i As Integer Dim sSheet1name As String sSheet1name = "Sheet1" '<<<<< For i = 2 To 32 'asumming that the first sheet in where the number is entered Worksheets(i).Range("J5").Formula = "='" & sSheet1name & "'!J5+" & i - 1 Next i End Sub '------------------copy end You copy this code above and paste it in to the macro editor. Do this as follows: 1. From the menu select Tools|Macro|Visual Basic Editor and this opens the Visual Basic Editor (VBE) window. 2. Then open the project explorer window in the VBE View|Project Explorer. 3. In that window find the VBAProject with the name of the workbook where your 32 sheets are and select it with one left click of the mouse and then right click the mouse and select Insert|Module. 3. This opens the module window and you can paste the code above in there. 4. Close the vbe (File|close and return to microsoft excel) 5. In excel save the file 6. Then run the macro with Tools|Macro|Marcos.... 7. A dialog box is displayed and select the macro doformula32sheets by doubleclicking it or selecting it and clicking run. It should be done. -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "STEVE" wrote: With the least amount of keystrokes, how can I have a number that is typed in cell J5 on 32 sheets in a workbook increase AUTOMATICALLY by one. On the first sheet, I type 1000 in cell J5. Automatically then I want cell J5 on sheet 2 to say 1001, and so on up to 1032. PLEASE GIVE ME EVERY STEP, since I have never entered a macro before,ie, I don't know exactly where to enter it, how to save it, how to run it, etc. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Numbering sheets in workbook | Excel Worksheet Functions | |||
invoice numbering | Excel Worksheet Functions | |||
Invoice numbers? | Excel Worksheet Functions | |||
Invoice Numbering | Excel Worksheet Functions | |||
Invoice Numbering | Excel Worksheet Functions |