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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
This code eliminates all of those selects:

<font face=Tahoma><SPAN style="color:#00007F">Public</SPAN> ps <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> Final_Code()
    ps = "wyg"
    <SPAN style="color:#00007F">With</SPAN> ActiveSheet
        .Unprotect ps
            <SPAN style="color:#00007F">With</SPAN> Range("A1:AG60")
                .Locked = <SPAN style="color:#00007F">True</SPAN>
                .FormulaHidden = <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            <SPAN style="color:#00007F">With</SPAN> 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")
                .Locked = <SPAN style="color:#00007F">False</SPAN>
                .FormulaHidden = <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        .EnableSelection = xlUnlockedCells
        .Protect ps
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
    Sheets("Job (1)").Copy Before:=Sheets(1)
    
    <SPAN style="color:#00007F">With</SPAN> ActiveSheet
        .Unprotect ps
            <SPAN style="color:#00007F">With</SPAN> Range("A1:AG60")
                .Locked = <SPAN style="color:#00007F">True</SPAN>
                .FormulaHidden = <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            <SPAN style="color:#00007F">With</SPAN> 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"))
                    .ClearContents
                    .Locked = <SPAN style="color:#00007F">False</SPAN>
                    .FormulaHidden = <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        .EnableSelection = xlUnlockedCells
        .Protect ps
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
    ActiveWorkbook.Save
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

To add 1 to H2 you can just use:

Code:
Range("H2").Value = Range("H2").Value +1

HTH,

Smitty
 
Upvote 0
cheers Smithy that helps alot,

one more question how do i get it ot copy the active worksheet and not Job (1) when startnig a new sheet?
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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