is there anyway to get a consecutive job number

MonkeyDeath

Board Regular
Joined
Jul 19, 2007
Messages
62
this is my code so far

Code:
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
This worked for me:

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

is that what you needed?

Chad
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top