is there anyway to get a consecutive job number

[MonkeyDeath]

this is my code so far

Sub Final_Code()
'
' Final_Code Macro
' Macro recorded 20/07/2007 by craig.monk
'

'
    ActiveWindow.SmallScroll Down:=-9
    Range("A20:AG27").Select
    ActiveSheet.Unprotect Password:="wyg"
    Range("A1:AG60").Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    Range( _
        "U57:AG60,K59:R59,K57:R57,C53:F53,C51:F51,C49:F49,C47:F47,C45:F45,J45:AA46,J47:AA48,J49:AA50,J51:AA52,J53:AA54,AD45:AF45,AD47:AF47,AD49:AF49,AD51:AF51,AD53:AF53,A36:AG40,G41:J41,A28:AG35,A20:AG27,AB16:AF16,AB14:AF14" _
        ).Select
    Range("AB14").Activate
    Selection.Locked = False
    Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
        False
    ActiveSheet.EnableSelection = xlUnlockedCells
    ActiveSheet.Protect Password:="wyg"
    Range("A20:AG27").Select
    Sheets("Job (1)").Select
    Sheets("Job (1)").Copy Before:=Sheets(1)
    ActiveSheet.Unprotect Password:="wyg"
    Range("A1:AG60").Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    Union(Range( _
        "F12:S12,F10:S10,F8:S8,F6:S6,H4:K4,F2,G2,H2,I2,S2:U2,S3:U3,S4:U4,U57:AG60,K59:R59,K57:R57,C53:F53,C51:F51,C49:F49,C47:F47,C45:F45,J45:AA46,J47:AA48,J49:AA50,J51:AA52,J53:AA54,AD45:AF45,AD47:AF47,AD49:AF49,AD51:AF51,AD53:AF53,A36:AG40,G41:J41" _
        ), Range( _
        "A28:AG35,A20:AG27,U19:W19,Z19:AB19,AB16:AF16,AB14:AF14,AB12:AF12,AB10:AF10,K16:P16,S16:T16,D16:E16,F14:S14" _
        )).Select
    Range("S4").Activate
    Selection.ClearContents
    Selection.Locked = False
    Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
        False
    ActiveSheet.EnableSelection = xlUnlockedCells
    ActiveSheet.Protect Password:="wyg"
    Range("H4:K4").Select
    ActiveWorkbook.Save
End Sub

what im after is in Cell H2 a number that is basically +1 of the previous jobsheet.

ie if im on job 34, run the above macro, i want it to automatically change Cell H2 to 35 and so on in the new job sheet, but maintains 34 on the previous sheet

 

[blazonQC]

This worked for me:

If ActiveSheet.Index > 1 Then
   ActiveSheet.Range("H2") = Worksheets(ActiveSheet.Index - 1).Range("H2").Value + 1
End If

is that what you needed?

Chad