Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,814
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Numbering Sheets incrementally (as in Invoice numbers)

Steve

The macro............

Sub Date_Increment()
Dim mynum As Long
Dim iCtr As Long
mynum = InputBox("Enter a number")
For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("J5")
.Value = mynum - 1 + iCtr
.NumberFormat = "0000"
End With
Next iCtr
End Sub

The instructions..................

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 workbook.

Run the macro by going to ToolMacroMacros.

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


Gord Dibben MS Excel MVP

On Tue, 12 Dec 2006 17:05:00 -0800, 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.


Gord Dibben MS Excel MVP
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
AUTOMATIC INVOICE NUMBERS Andreas Excel Discussion (Misc queries) 1 March 8th 06 07:59 PM
Numbering sheets in workbook Red Excel Worksheet Functions 3 March 2nd 05 09:10 PM
Invoice numbers? Missie Excel Worksheet Functions 1 December 13th 04 08:28 AM
Invoice Numbering Kevin Excel Worksheet Functions 1 November 16th 04 04:59 PM
Invoice Numbering Kevin Mayfield Excel Worksheet Functions 0 November 16th 04 03:49 PM


All times are GMT +1. The time now is 06:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"