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

I would use John Walkenbach's SheetOffset function here
http://www.j-walk.com/ss/excel/tips/tip63.htm

and I will include John's code that can be copied directly into a VBA module:

Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function


In your first worksheet, put 1000 in cell J5, then select your other 31
sheets (hold down the control key and use the mouse to select all of the
other sheets) and enter
=sheetoffset(-1, J5)+1
in cell J5, which will be entered in all 31 sheets. Hit enter.

For tutorials on navigating the VBA editor see David McRitchies site (The
first two links are to the two tutorials that I think will help you the most,
the third is the main page):

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


"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
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
SET UP AUTOMATIC INVOICE NUMBERS nothingnice Excel Discussion (Misc queries) 2 January 10th 06 11:00 PM
How do I set up an automated numbering system For my "Invoice Num Frank Medina & Associates Excel Worksheet Functions 1 August 14th 05 04:23 PM
Numbering sheets in workbook Red Excel Worksheet Functions 3 March 2nd 05 09:10 PM
invoice numbering Mel Excel Worksheet Functions 2 January 3rd 05 10:15 PM
Invoice numbers? Missie Excel Worksheet Functions 1 December 13th 04 08:28 AM


All times are GMT +1. The time now is 10:53 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"