incremented numbering

[tourpro]

i need a little more detail on how to do this...
1. what is a general module in your workbook?
2. Copy/paste this Function into a single cell or?
3. Select second sheet then SHIFT + Click on last sheet to group these
sheets. not sure what this means or excatly how to do this...


Assuming L7 on each sheet is where you want the incremented number..........

Copy/paste this Function to a general module in your workbook.

Function PrevSheet(rg As Range)
Application.Volatile
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Enter the number 1 in L7 on first sheet.

Select second sheet then SHIFT + Click on last sheet to group these sheets.

Select L7 on active sheet and enter this formula which will be entered on all
selected sheets.

IF(K10>0,prevsheet(L7) +1,"")

Ungroup the sheets and you're done.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

See in-line replies.

i need a little more detail on how to do this...
1. what is a general module in your workbook?

Assuming your workbook is open.

Alt + F11 to get to Visual Basic Editor.

CTRL + r to open Project Explorer.

Right-click on your workbook(project) and Insert>Module.

Paste the PrevSheet code into that module.

2. Copy/paste this Function into a single cell or?

Since you are not the OP, I cannot say where to enter the formula but the
original example should give some clue to you.

After entering the number 1 in L7 of Sheet1, select Sheet2 then hold SHIFT key
and Click(with mouse) on last sheet to select all sheets but Sheet1.

Select L7 on active sheet and enter this formula which will be entered on all
selected sheets.

IF(K10>0,prevsheet(L7) +1,"")

Ungroup the sheets and you're done.

3. Select second sheet then SHIFT + Click on last sheet to group these
sheets. not sure what this means or excatly how to do this...

See number 2 above.

To "ungroup" the sheets, select Sheet1 or right-click on any sheet and "ungroup
sheets".


Gord

 

[Mike H]

Hi,

Taking your question in turn:-

1. what is a general module in your workbook?

To create a general module.
Alt +F11 to open VB editor
Right click 'this workbook' amd insert Module
You have now created a general module in your workbook it is the white pane
on the right hand side.

2. Copy/paste this Function into a single cell or?

Paste the code below into that general module

3. Select second sheet then SHIFT + Click on last sheet to group these
sheets. not sure what this means or excatly how to do this...

click on the sheet tab for the second sheet from the left and then press an
hold the shift key and click on the rightmost sheet tab and you will have
selected all the sheets in your workbook except the first one.

Select L7 on the sheet you can see and enter the formula given to you

To ungroups sheet click on any sheet tab

HTH

Mike

 

[tourpro]

maybe i'm missing something but after i create my general module i put a 1 in
L7 on sheet1 and then i past this code into L7 on sheet2, sheet3, sheet4 all
i get on each sheet is:
IF(K10>0,prevsheet(L7) +1,"") in the cell.
i put number 1 in L7 on sheet1 but on sheet2 it does not become number 2 in
L7 and 3 on sheet2 and so on

 

[Gord Dibben]

You created the general module and pasted the PrevSheet function into that
module.

You entered a 1 in L7 of sheet1.

You grouped sheet2 with rest of sheets.

You entered =IF(K10>0,prevsheet(L7) +1,"")

If you are getting the formula showing maybe the cells were pre-formatted as
text.

Re-format to General then re-enter the formula on sheet2 with the sheets
grouped.

Or, if as your example shows, you did not precede the formula with an = sign
then do so.


Gord