code error - memory error

Please can anyone help - my PC runs out of memory (I currently have
512) I'm presuming that my code is badly written - can anyone please
help with this, code follows:


Private Sub CommandButton1_Click()

    
    ' Option box allowing user to select relevant ctr

    filetoopen = Application _
        .GetOpenFilename("Excel Files (*.xls), *.xls")
    If filetoopen <> False Then
        Workbooks.Open Filename:= _
        filetoopen
    End If

    Dim strPath As String, strFileName As String
    
    strPath = ActiveWorkbook.FullName
    strFileName = Mid(strPath, InStrRev(strPath, "\") + 1)
        
' manipulating ctr data
    
    Workbooks(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("D:D").Select
    Selection.Copy
    Workbooks("HRCN_EXT_DATA1test2.xls").Activate
    Sheets("CTR_DATA").Select
    Columns("A:A").Select
    ActiveSheet.Paste
    
    Workbooks(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("F:F").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Columns("B:B").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("G:H").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("C1").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("J:L").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("E1").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("M:O").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("H1").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    ActiveWindow.SmallScroll ToRight:=8
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("Q:S").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("K1").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll ToRight:=8
    
    Windows(strFileName).Activate
    ActiveWindow.SmallScroll ToRight:=5
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("Y:Y").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("N1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Windows(strFileName).Activate
    ActiveWindow.SmallScroll ToRight:=5
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("AC:AC").Select
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("O1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("P1").Select
    
    Windows(strFileName).Activate
    ActiveWindow.SmallScroll ToRight:=14
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("AH:AH").ColumnWidth =
13.29
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("AH:AH").Select
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("AP:AQ").Select
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Application.CutCopyMode = False
    Range("Q1").Select
    
    Windows(strFileName).Activate
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    With Application
        .Calculation = xlManual
        .MaxChange = 0.001
    End With
    ActiveWorkbook.PrecisionAsDisplayed = False
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("AH:AH").Select
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("P1").Select
    ActiveSheet.Paste
    Range("S1").Select
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("AS:AT").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    ActiveSheet.Paste
    ActiveWindow.SmallScroll ToRight:=9
    
    Windows(strFileName).Activate
    ActiveWindow.SmallScroll ToRight:=9
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("BB:BC").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("U1").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    ActiveWindow.SmallScroll ToRight:=11
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("BL:BL").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("W1").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("BN:BN").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("X1").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    ActiveWindow.SmallScroll ToRight:=6
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    ActiveWindow.SmallScroll ToRight:=4
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("BO:BR").Select
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("BO:BS").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("Y1").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("BT:BU").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("AD1").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll ToRight:=5
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("BX:BX").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("AF1").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Range("CA1").Select
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("CA:CA").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("AG1").Select
    ActiveSheet.Paste
        
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Range("CF1").Select
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Worksheets("CTR_DATA").Columns("AH:AH").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Range("CG1").Select
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Worksheets("CTR_DATA").Columns("AI:AI").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("CF:CG").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    ActiveSheet.Paste
        
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("CJ:CM").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("AJ1").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("CN:CP").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("AN1").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Range("ET1").Select
    Selection.Copy
         
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("ES:EV").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("AQ1").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll ToRight:=6
    
    Windows(strFileName).Activate
    ActiveWindow.SmallScroll ToRight:=9
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("EW:EY").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("AU1").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll ToRight:=6
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("EZ:FB").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("AX1").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("FC:FD").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("BA1").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("FI:FJ").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("BC1").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Range("FL1").Select
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("FK:FK").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("BE1").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    ActiveWindow.SmallScroll ToRight:=7
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("FQ:FQ").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("BF1").Select
    ActiveSheet.Paste
        
    Windows(strFileName).Activate
    ActiveWindow.SmallScroll ToRight:=4
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("FU:FU").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("BG1").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("FW:FW").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("BH1").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    ActiveWindow.SmallScroll ToRight:=7
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("FY:FY").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("BI1").Select
    ActiveSheet.Paste
    
    Windows(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("GA:GA").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("HRCN_EXT_DATA1test2.xls").Activate
    Range("BJ1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Calculate
    Sheets("CTR_DATA").Select
    Range("a1").Activate
    
    'Saves worksheet

    ActiveWorkbook.Save
    
    'closes workbook ctr without saving
    
   Workbooks(strFileName).Close (False)

    
'Deletes all data in Todays HRCN tab

Workbooks("HRCN_EXT_DATA1test2.xls").Activate
Sheets("Todays HRCN's").Select
Application.CutCopyMode = False
Selection.ClearContents

'Copy formulas in workings tab down to 5000 line

    Workbooks("HRCN_EXT_DATA1test2.xls").Activate
        
    Worksheets("Workings").Select
    Worksheets("Workings").Range("A2.BM2").Cells.Select
    
    
    
    Selection.AutoFill
Destination:=Worksheets("Workings").Range("A2.BM5000"),
Type:=xlFillDefault
    Worksheets("Workings").Range("A2.BM5000").Cells.Select
    Worksheets("Workings").Calculate

'Copy workings tab

Workbooks("HRCN_EXT_DATA1test2.xls").Activate
Sheets("Workings").Cells.Copy
  
' Paste values to Todays HRCN tab

    Sheets("Todays HRCN's").Range("A1").PasteSpecial _
    Paste:=xlValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Sheets("Todays HRCN's").Range("A1").PasteSpecial _
    Paste:=xlFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    
 
'Replaces every occurrence of the #N/A, REF! etc
'with the relevant symbol.

Worksheets("Todays HRCN's").Columns("A:ez").Replace _
    What:="&#163;", Replacement:="�", _
    SearchOrder:=xlByColumns, MatchCase:=True

Worksheets("Todays HRCN's").Columns("A:ez").Replace _
    What:="&#039;", Replacement:="'", _
    SearchOrder:=xlByColumns, MatchCase:=True
    
Worksheets("Todays HRCN's").Columns("A:ez").Replace _
    What:="&#034;", Replacement:="""", _
    SearchOrder:=xlByColumns, MatchCase:=True

Worksheets("Todays HRCN's").Columns("A:ez").Replace _
    What:="#N/A", Replacement:="", _
    SearchOrder:=xlByColumns, MatchCase:=True
    
Worksheets("Todays HRCN's").Columns("A:ez").Replace _
    What:="#REF!", Replacement:="", _
    SearchOrder:=xlByColumns, MatchCase:=True
       
'Deletes the External Data and ctr data ready for next use
 
Workbooks("HRCN_EXT_DATA1test2.xls").Activate
Sheets("External Data - Payroll Query").Range("ExData").Clear
Sheets("CTR_DATA").Range("ctr").Clear

'Saves worksheet

ActiveWorkbook.Save

'Filters on column BM - format has to be dd/mmm/yyyy due to problem
interpreting date

Workbooks("HRCN_EXT_DATA1test2.xls").Activate
Worksheets("Todays HRCN's").Select
Worksheets("Todays HRCN's").Rows("1:1").Select
    
    HRCNDate = InputBox("Please input HRCN date, dd/mmm/yyyy eg
04/Apr/2002")
                          
             Selection.AutoFilter
             Selection.AutoFilter Field:=65, Criteria1:=HRCNDate
             
           'displays the message box
           
   a = MsgBox("Do you want to filter again?", vbYesNo)
   If a = vbYes Then
   Selection.AutoFilter
   
    HRCNDate = InputBox("Please input HRCN date, dd/mmm/yyyy eg
04/Apr/2001")
    
' Filters on user option if yes selected

    Worksheets("Todays HRCN's").Select
    Worksheets("Todays HRCN's").Rows("1:1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=65, Criteria1:=HRCNDate
    Else
    
    'If user doent want to filter again
    
    Sheets("Todays HRCN's").Select
    Sheets("Todays HRCN's").Cells.Select
    Selection.Copy
    
    'Add new workbook and call it Todays HRCN
    Workbooks.Add
    ActiveSheet.Paste

    Application.CutCopyMode = False
    
    'Rename sheets, payroll area tabs
    
    'Screen updating hide
    
    Application.ScreenUpdating = False

    
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "HRCN_For_Payroll"
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "001"
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "003"
    Sheets.Add
    Sheets("Sheet4").Select
    Sheets("Sheet4").Name = "005"
    Sheets.Add
    Sheets("Sheet5").Select
    Sheets("Sheet5").Name = "007"
    Sheets.Add
    Sheets("Sheet6").Select
    Sheets("Sheet6").Name = "015"
    Sheets.Add
    Sheets("Sheet7").Select
    Sheets("Sheet7").Name = "01T"
    Sheets.Add
    Sheets("Sheet8").Select
    Sheets("Sheet8").Name = "023"
    Sheets.Add
    Sheets("Sheet9").Select
    Sheets("Sheet9").Name = "037"
    Sheets.Add
    Sheets("Sheet10").Select
    Sheets("Sheet10").Name = "040"
    Sheets.Add
    Sheets("Sheet11").Select
    Sheets("Sheet11").Name = "044"
    Sheets.Add
    Sheets("Sheet12").Select
    Sheets("Sheet12").Name = "060"
    Sheets.Add
    Sheets("Sheet13").Select
    Sheets("Sheet13").Name = "061"
    Sheets.Add
    Sheets("Sheet14").Select
    Sheets("Sheet14").Name = "065"
    Sheets.Add
    Sheets("Sheet15").Select
    Sheets("Sheet15").Name = "069"
    Sheets.Add
    Sheets("Sheet16").Select
    Sheets("Sheet16").Name = "071"
    Sheets.Add
    Sheets("Sheet17").Select
    Sheets("Sheet17").Name = "079"
    Sheets.Add
    Sheets("Sheet18").Select
    Sheets("Sheet18").Name = "080"
    Sheets.Add
    Sheets("Sheet19").Select
    Sheets("Sheet19").Name = "081"
    Sheets.Add
    Sheets("Sheet20").Select
    Sheets("Sheet20").Name = "082"
    Sheets.Add
    Sheets("Sheet21").Select
    Sheets("Sheet21").Name = "086"
    Sheets.Add
    Sheets("Sheet22").Select
    Sheets("Sheet22").Name = "089"
    Sheets.Add
    Sheets("Sheet23").Select
    Sheets("Sheet23").Name = "090"
    Sheets.Add
    Sheets("Sheet24").Select
    Sheets("Sheet24").Name = "091"
    Sheets.Add
    Sheets("Sheet25").Select
    Sheets("Sheet25").Name = "092"
    Sheets.Add
    Sheets("Sheet26").Select
    Sheets("Sheet26").Name = "093"
    Sheets.Add
    Sheets("Sheet27").Select
    Sheets("Sheet27").Name = "094"
    Sheets.Add
    Sheets("Sheet28").Select
    Sheets("Sheet28").Name = "095"
    Sheets.Add
    Sheets("Sheet29").Select
    Sheets("Sheet29").Name = "096"
    Sheets.Add
    Sheets("Sheet30").Select
    Sheets("Sheet30").Name = "097"
    Sheets.Add
    Sheets("Sheet31").Select
    Sheets("Sheet31").Name = "11T"
    Sheets.Add
    Sheets("Sheet32").Select
    Sheets("Sheet32").Name = "157"
    Sheets.Add
    Sheets("Sheet33").Select
    Sheets("Sheet33").Name = "160"
    Sheets.Add
    Sheets("Sheet34").Select
    Sheets("Sheet34").Name = "193"
    Sheets.Add
    Sheets("Sheet35").Select
    Sheets("Sheet35").Name = "194"
    Sheets.Add
    Sheets("Sheet36").Select
    Sheets("Sheet36").Name = "195"
    Sheets.Add
    Sheets("Sheet37").Select
    Sheets("Sheet37").Name = "201"
    Sheets.Add
    Sheets("Sheet38").Select
    Sheets("Sheet38").Name = "202"
    Sheets.Add
    Sheets("Sheet39").Select
    Sheets("Sheet39").Name = "203"
    Sheets.Add
    Sheets("Sheet40").Select
    Sheets("Sheet40").Name = "204"
    Sheets.Add
    Sheets("Sheet41").Select
    Sheets("Sheet41").Name = "206"
    Sheets.Add
    Sheets("Sheet42").Select
    Sheets("Sheet42").Name = "207"
    Sheets.Add
    Sheets("Sheet43").Select
    Sheets("Sheet43").Name = "208"
    Sheets.Add
    Sheets("Sheet44").Select
    Sheets("Sheet44").Name = "209"
    
       
    'Finds and replaces all direct/indirect data for global Y/N
    
    Sheets("HRCN_For_Payroll").Columns("AJ:AK").Replace _
    What:="Y", Replacement:="Indirect", _
    SearchOrder:=xlByColumns, MatchCase:=True

    Worksheets("HRCN_For_Payroll").Columns("AJ:AK").Replace _
    What:="N", Replacement:="Direct", _
    SearchOrder:=xlByColumns, MatchCase:=True
    
    'renames the column heading
    
    Worksheets("HRCN_For_Payroll").Rows("1:1").Replace _
    What:="Directew Direct/Indirect", Replacement:="Direct/Indirect",
_
    SearchOrder:=xlByColumns, MatchCase:=True
      
        
    'Sort and filter new HRCN for payroll into separate payroll tabs
    
    '001
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "001"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=001", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 001 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("001").Select
    Sheets("001").Paste
    Application.CutCopyMode = False
    
    '003
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "003"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=003", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 003 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("003").Paste
    Application.CutCopyMode = False
    
    '005
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "005"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=005", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 005 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("005").Select
    Sheets("005").Paste
    Application.CutCopyMode = False
    
    '007
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "007"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=007", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 007 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("007").Paste
    Application.CutCopyMode = False
    
    '015
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "015"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=015", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 015 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("015").Paste
    Application.CutCopyMode = False
    
    '01T
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "01T"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=01T", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 01T tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("01T").Paste
    Application.CutCopyMode = False
    
    '023
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "023"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=023", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 023 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("023").Paste
    Application.CutCopyMode = False
    
    '037
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "037"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=037", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 037 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("037").Paste
    Application.CutCopyMode = False
    
    '040
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "040"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=040", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 040 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("040").Paste
    Application.CutCopyMode = False
    
    '044
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "044"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=044", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 044 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("044").Paste
    Application.CutCopyMode = False
    
    '060
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "060"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=060", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 060 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("060").Paste
    Application.CutCopyMode = False
    
    '061
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "061"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=061", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 061 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("061").Paste
    Application.CutCopyMode = False
    
    ActiveWorkbook.Save
    
    
    '065
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "065"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=065", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 065 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("065").Paste
    Application.CutCopyMode = False
    
    
    
    '069
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "069"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=069", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 069 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("069").Paste
    Application.CutCopyMode = False
    
    '071
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "071"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=071", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 071 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("071").Paste
    Application.CutCopyMode = False
    
    '079
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "079"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=079", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 079 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("079").Paste
    Application.CutCopyMode = False
    
    '080
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "080"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=080", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 080 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("080").Paste
    Application.CutCopyMode = False
    
    '081
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "081"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=081", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 081 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("081").Paste
    Application.CutCopyMode = False
    
    '082
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "082"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=082", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 082 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("082").Paste
    Application.CutCopyMode = False
    
    '086
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "086"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=086", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 086 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("086").Paste
    Application.CutCopyMode = False
    
    '089
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "089"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=089", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 089 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("089").Paste
    Application.CutCopyMode = False
    
    '090
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "090"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=090", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 090 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("090").Paste
    Application.CutCopyMode = False
    
    '091
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "091"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=091", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 091 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("091").Paste
    Application.CutCopyMode = False
    
    '092
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "092"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=092", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 092 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("092").Paste
    Application.CutCopyMode = False
    
    '093
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "093"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=093", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 093 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("093").Paste
    Application.CutCopyMode = False
    
    '094
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "094"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=094", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 094 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("094").Paste
    Application.CutCopyMode = False
    
    '095
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "095"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=095", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 095 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("095").Paste
    Application.CutCopyMode = False
    
    '096
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "096"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=096", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 096 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("096").Paste
    Application.CutCopyMode = False
    
    '097
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "097"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=097", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 097 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("097").Paste
    Application.CutCopyMode = False
    
    '11T
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "11T"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=11T", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 11T tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("11T").Paste
    Application.CutCopyMode = False
    
    '157
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "157"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=157", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 157 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("157").Paste
    Application.CutCopyMode = False
    
    '160
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "160"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=160", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 160 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("160").Paste
    Application.CutCopyMode = False
    
    '193
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "193"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=193", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 193 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("193").Paste
    Application.CutCopyMode = False
    
    '194
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "194"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=194", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 194 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("194").Paste
    Application.CutCopyMode = False
    
    '195
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "195"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=195", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 195 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("195").Paste
    Application.CutCopyMode = False
       
    '201
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "201"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=201", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 201 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("201").Paste
    Application.CutCopyMode = False
    
    '202
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "202"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=202", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 202 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("202").Paste
    Application.CutCopyMode = False
    
    '203
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "203"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=203", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 203 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("203").Paste
    Application.CutCopyMode = False
    
    '204
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "204"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=204", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 204 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("204").Paste
    Application.CutCopyMode = False
    
    '206
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "206"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=206", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 206 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("206").Paste
    Application.CutCopyMode = False
    
    '207
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "207"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=207", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 207 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("207").Paste
    Application.CutCopyMode = False
    
    '208
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "208"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=208", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 208 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("208").Paste
    Application.CutCopyMode = False
    
    '209
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
    Worksheets("HRCN_For_Payroll").Select
    Worksheets("HRCN_For_Payroll").Rows("1:1").Select 'FILTER on
custom "209"
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="=209", Operator:=xlAnd
    Sheets("HRCN_For_Payroll").Select  'copy and paste to 209 tab
    Sheets("HRCN_For_Payroll").Cells.Select
    Selection.Copy
    Sheets("209").Paste
    Application.CutCopyMode = False
       
    'Removes filter from HRCN tab
    
    Worksheets("HRCN_For_Payroll").Select
    Selection.AutoFilter
        
    'Save new workbook (TodaysHRCN.xls) into relevant folder
    
    ChDir "G:\Hris\Rachel\HRCN Data for Payroll\HRCN For Payroll"
    ActiveWorkbook.SaveAs Filename:= _
        "G:\Hris\Rachel\HRCN Data for Payroll\HRCN For
Payroll\TodaysHRCN.xls", FileFormat _
        :=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
        False, CreateBackup:=False
           
    End If
        
        'closes workbook HRCN without saving
    
   Workbooks("HRCN_EXT_DATA1test2.xls").Close (False)
   
End Sub

Everything was fine with this until i added all the worksheets to
"Today's HRCN" and started populating them - originally I only had 2
worksheets and copied and pasted into them (One tab held 001 - 097,
second tab 11T - 209, all individual tabs)

Any help would be greatly appreciated.

Thanks
0
curran_rachel (1)
6/1/2004 9:40:49 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1219 Views

Similar Articles

[PageSpeed] 40
See related articles to this posting 


Your code is too long to check.. 
Couldn't you go step by step : for exemple, try to put some break
points every "chapter" and check your memory via Task Manager to see
where it starts messing...


---
Message posted from http://www.ExcelForum.com/
0
Pierre.1761rf (1)
6/1/2004 9:51:17 AM 
You do a lot of selecting and activating.  You don't usually need to do that.

This kind of stuff:

    Workbooks(strFileName).Activate
    Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("D:D").Select
    Selection.Copy
    Workbooks("HRCN_EXT_DATA1test2.xls").Activate
    Sheets("CTR_DATA").Select
    Columns("A:A").Select
    ActiveSheet.Paste

Could be replaced with

with  workbooks(strfilename).worksheets("PUBLIC_XXHRS_PERSON_V")
       .Columns("D:D").copy _
          destination:=Workbooks("HRCN_EXT_DATA1test2.xls") _
                             .workSheets("CTR_DATA").range("a1")
   	
       .Columns("F:F").copy _
            destination:=Workbooks("HRCN_EXT_DATA1test2.xls") _
                            .workSheets("CTR_DATA").range("a1")
end with

And if those cells are not formulas, you could just grab the whole mess at once:

With Workbooks(strFileName).Worksheets("PUBLIC_XXHRS_PERSON_V")
        .Range("D:D,F:H,J:O,Q:S,Y:Y,AC:AC,AH:AH,AP:AQ,AS:AT,BB:BC,BL:BL," & _
                  "BN:BU,BX:BX,CA:CA,CF").Copy _
            Destination:=Workbooks("HRCN_EXT_DATA1test2.xls") _
                              .Worksheets("CTR_DATA").Range("a1")
end with

But watch those columns!  I'm not sure I transposed all of them.

And one way to cycle through all those new worksheet names:

Dim mySheetNames As Variant
Dim iCtr As Long
Dim newWkbk As Workbook
Dim wks As Worksheet

mySheetNames = Array("HRCN_For_Payroll", "001", "003", _
                     "005", "007", "015", "01T", "023", "037", _
                     "040", "044", "060", "061", "065", "069", _
                     "071", "079", "080", "081", "082", "086", _
                     "089", "090", "091", "092", "093", "094", _
                     "095", "096", "097", "11T", "157", "160", _
                     "193", "194", "195", "201", "202", "203", _
                     "204", "206", "207", "208", "209")

Set newWkbk = Workbooks.Add(1)
ActiveSheet.Name = "Deleteme"

For iCtr = LBound(mySheetNames) To UBound(mySheetNames)
    Set wks = newWkbk.Worksheets.Add
    wks.Name = mySheetNames(iCtr)
Next iCtr

application.displayalerts = false
newWkbk.worksheets("deleteme").delete
application.displayalerts = true

========
Earlier you have some code that looks like this:


Worksheets("Todays HRCN's").Columns("A:ez").Replace _
    What:="&#163;", Replacement:="�", _
    SearchOrder:=xlByColumns, MatchCase:=True

Worksheets("Todays HRCN's").Columns("A:ez").Replace _
    What:="&#039;", Replacement:="'", _
    SearchOrder:=xlByColumns, MatchCase:=True
    
Worksheets("Todays HRCN's").Columns("A:ez").Replace _
    What:="&#034;", Replacement:="""", _
    SearchOrder:=xlByColumns, MatchCase:=True

Worksheets("Todays HRCN's").Columns("A:ez").Replace _
    What:="#N/A", Replacement:="", _
    SearchOrder:=xlByColumns, MatchCase:=True
    
Worksheets("Todays HRCN's").Columns("A:ez").Replace _
    What:="#REF!", Replacement:="", _
    SearchOrder:=xlByColumns, MatchCase:=True


You could put this into a loop.

Dim myFromChars As Variant
Dim myToChars As Variant
Dim iCtr As Long

myFromChars = Array("&#163", "&#039", "&#034", "#N/A", "#REF!")
myToChars = Array("�", "'", """", "", "")

With Worksheets("Todays HRCN's").Columns("A:ez")
    For iCtr = LBound(myFromChars) To UBound(myFromChars)
        .Replace what:=myFromChars(iCtr), replacement:=myToChars(iCtr), _
            SearchOrder:=xlByColumns, MatchCase:=True
    Next iCtr
end with

You have other spots that you do Edit|replace, too.


Debra Dalgleish has a way to split up rows to other sheets--but she uses
advanced filter and loops through all the values.

You may want to look at how she did it.

It sounds like you want to steal some code from Debra Dalgleish's site:

http://www.contextures.com/excelfiles.html

Look for:

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- creates a list of unique items, 
creates a sheet for each item, then replaces old data with current. 
AdvFilterCity.xls 46 kb 

and

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb 


Rachel Curran wrote:
> 
> Please can anyone help - my PC runs out of memory (I currently have
> 512) I'm presuming that my code is badly written - can anyone please
> help with this, code follows:
> 
<<snipped>>

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
6/2/2004 12:36:26 AM
Reply:

Similar Artilces:

Error attatching a product to an opportunity
I get the following script error when I link a product to an opportunity: Line: 151 Char: 4 Error: "crmForm.pricelefelid.value is null or not an object" Did you set the Opportunity Price List value? This field is listed next to Potential Customer field. Frank Lee Workopia, Inc. http://www.workopia.com/Links.htm San Francisco, CA "Mike" wrote: > I get the following script error when I link a product to > an opportunity: > > Line: 151 > Char: 4 > Error: "crmForm.pricelefelid.value is null or not an > object" > ...

Custom Archive code -- modify my code!
Hi - For whatever reason, my company doesn't want us to save our emails and has made backing them up as manual as possible. With ALOT of help from Jimmy Pena at www.codeforexcelandoutlook.com (an excellent site!), I've made a good start at creating a procedure to do this but have hit a wall and need some help. The current version of the code is below ... please note that I'm very new at Outlook VB so the changes I made to Jimmy's original code are probably pretty ugly. All input is welcome ... I'm using OL-07 and Windows XP ... currently, the code does this:...

Forware error
In outlook 2003 I have set up a rule to forward all incomming mails to a pop3 accaunt. In the "sent items" the mails are listed as sent, and the icon for forward is showen in the inbox, but the is no mails in the pop3 account. It is a Exchange 2003 that serves the mail. what is wrong? ...

About clipboard memory
I couldnot copy or paste anything. I copied a word or anything. When I want to paste it, it provides the only word "Hello!". So, is there any way to get out of this trouble? Please reply me! ...

Anti-Virus Error Message in Outlook 2003?
I have replaced AVG & Panda AV with Avast 4 recently. I am using Windows XP Pro on my home network. When Outlook 2003 loads on my settings(Administrator) there are no problems. Everything works just fine =^) When it loads on my wife's settings(Administrator)she gets the following error messages when loading Outlook 2003: "Addin (Panda) could not be installed or loaded" "Addin (AVG) could not be installed or loaded" Never got them before using Avast 4. I like Avast =) Only happens on wife's settings! I have deleted all files containing AVG/Panda info. What wil...

Pub 2003 Clip Organizer Error
Sorry if this has been answered before - search didn't find this error in Pub 2003. Upgraded Pub 2003 Beta to Pub 2003. Clip Organizer gets "Error Code 0x007007f" Tried repair function - no help. Search on MS came up empty. Have since found that I cannot import Excel list for mail merge AND cannot create merge list within Publisher (Access). Do I need to uninstall Pub and do a clean install? -- Donna McBryde =^..^= 99.9% of the world's problems would cease to exist if everyone lived by the Golden Rule. -- There are two means of refuge from the miseries of life: music an...

Subroutine (Delete Name Ranges) Errors "sometimes"
Using XL 2003 & 97 60 % of the time the subroutine below; works fine. Other times it errors on Name (wanting a definition) I have not "Dim" ed "Name" previous to this loop thinking that "Name" is a system thing. This may be my error. Also, I am not sure how to error trap the routine - if no names exist. I used "On Error Resume Next" and it still errored Sub DeleteAllNames() 'ALL sheets For Each Name In Names Name.Delete Next Name End Sub Any thoughts? TIA Dennis Determined that the following macro must be run from a ...

Error 1722 when opening outlook
When opening outlook i get a "error 1722 there is a problem with this windows installer package..." It started as a office 2000 problem, I upgraded to XP, and loaded all the service packs still didnt work Unistalled deleted the office folder and reinstalled still have the same issue. The user account has power user priv. If i change it to admin it works fine... Any suggestions? ...

i am receiving error while downloading my emails but i can send
hello everyone, I have a question for you. I am not able to receive emials from our email server.but i can send the email successfully. Any i dea.? i am stuck here. Thanks for yr help. Thanks, Bharat. bharat wrote: > > hello everyone, > > I have a question for you. > > I am not able to receive emials from our email server.but i can send the > email successfully. > Any i dea.? > i am stuck here. > > Thanks for yr help. > > Thanks, > Bharat. > > What is the exact error message you are receiving? ...

Pay Code
We have full time regular employees that worked on a holiday and also received their full time regular salary for the holiday and the the 8 hours for the holiday. The holiday code is configured to reduce; therefore, do I need to create a "holiday work" pay code to ensure these employees get paid correctly. Thanks, rc. ...

no memory
Hi, When I try work with two large excel' document (2x 22MB) at ones, I get an error message 'no memory'. This message does not prevent me to work with the files but calculation/changes tend to take longer, even the occational crash. Is this a hardware problem? If so what needs to be upgraded? Working Memory,..? Thanks Frank It's only a guess with what you've given me to go on, but I'd say you need more memory. P "Frank" <frank.van.hyfte@pandora.be> wrote in message news:0a3b01c3892e$c9c0e9f0$a401280a@phx.gbl... > Hi, > > When I try ...

Access 97
I've been using Access 97 for database management in a medical office for several years now and the company that supports the database has not been able to correct errors that occur during a conversion to updated Access versions. They work solely in Access 97 and I therefore must keep this version. So, I use a split on the cpu... running Access 97 and xp... it's been working fine. However, just recently, every time I open Access 97, I get an 'out of memory' error. There os definately a lot of memory and I've shut down almost everything and then tried opening i...

VBA format function codes
I thought I understood the VBA Format function codes, but apparantly not. I have several columns of numbers that I want to write to a file so that they line up. Some of them are integers, some have a few decimal places. For the integers, I first tried "####0": ?format(123,"####0") & "|" 123| I forgot that "#" does not insert spaces. Next I tried "@@@@0": ?format(123,"@@@@0") & "|" 1230| Now I get the spaces, but why am I getting the extra zero? Next I tried "@@@@@": ...

Error when selecting advanced role in Business Portal
I have installed Business Portal. However, I cannot see any queries/reports. I have granted all business portal roles to myself. I am trying to include an advanced role for myself in application settings>business portal users. However, I get an error there "No constituent roles are defined. There are no associations to modify". How to resolve this. Right now, I cannot see any great plains data. I cannot see any predefined queries and reports. ...

File lock error
Hello all, We have users that work on MS Publisher 2003 documents on a shared folder. We keep getting an error when multiple users try to access the same file. The error is a file lock error. My question is this - Can MS publisher documents be shared so that multiple users can work on them ?? The NOS is Novell netware 6.5 Sp5. All clients are windows XP. thanks for any help in advance, Martin Reyes LHRIC ...

Date Value Error
Hi, I am trying to calculate the number of days between 2 dates, I am sure in the past I have simply just did a formula to say date in cell 1 - date in cell 2, however I get a value error. The dates are formatted i nthe following way 18/11/2009 for example and I just need to calculate the number of days between 2 dates. Many thanks Paula Hi Paula, check if you don't have any spaces, simple formula should work =B1-A1 "Paula" wrote: > Hi, > > I am trying to calculate the number of days between 2 dates, I am sure in > the past I have simpl...

error when sending emails from entourage
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Mail could not be received at this time <br><br>The server for account &quot;myaccounthere&quot; returned the error &quot;authentication failed.&quot; Your username/password or security settings may be incorrect. <br><br>So basically I can't send any mail from entourage. Its asking for a password but where is it getting this password from?!! How can I reset this password? Has this error message only shown up recently? If you send an e-mail message, some accounts requir...

Outlook Error Message When Starting Outlook
Hi: When I start Outlook 2002, and when login to my POP3 account, the following message is displayed: "A program is trying to access e-mail addresses you have stored in Outlook". I have Norton Internet Security 2004. I've disabled it but the message appears. I don't know what is happening or what is causing this message to appear. Thank you very much. David http://office.microsoft.com/assistance/preview.aspx?AssetID=HA011127891033&CTT=1&Origin=EC010230001033&QueryID=iPg50Uwgn I hope this might help. "David" wrote: > Hi: > > When I...

Error Code 0x80070005
I tried 3 times to install windows xp service pack 2 and the installation was stopped and I received a message that said "Access Denied" Installation was stopped, installation failed. How can I upgrade the system so I can put Microsoft Office Access 2007 software on my system? That's a permission error (access denied), try resetting the security settings back to the defaults: How to reset security settings back to the defaults <http://support.microsoft.com/kb/313222> Make sure you're running as an administrator. -- The 0x80070005 is an access deni...

Exchange Server 2007 Hub Transport errors
Good Morning everyone, I have an issue with trying to submit mail. When I draft a message and press send in OWA or via MAPI (Outlook 2003 & Outlook 2007), the message ends up in the DRAFTS folder. (NOT THE SENT ITEMS or the OUTBOX). When troubleshooting via the ExTA there is a reference to the MSExchangeTransport service not starting. When I attempt to start the service, it will start; however, it stops "on it's own" without any intervention. When I check the Event Viewer this is what I see: Source: MSExchangeTransport Category: Storage Event ID: 17002 Descri...

Error on hyperlinks to external websites
When I create a hyperlink to an external website in excel and click it it gives the following error message "This operation has been cancelled due to restrictions in effect on this computer". I'm not sure whether this is an issue with Excel or with Internet Explorer. Has anybody had a similar problem and how was it resolved. Thank you ...

Close Task gives an error but completes.
I just completed an upgrade from 1.2 to 3.0. When any task activity is completed (i.e. "Close Task" is chosen under Actions), an error dialog appears stating "An error has occurred. For more information, contact your system administrator." Nothing is logged in the event viewer on the server, and the task is actually closed. It's just disconcerting to the users. Anybody else experiencing this or have any suggestions? open up the web.config and turn on developer errors then save the web.config and restart IIS. You can find the web.config under c:\inetpub\wwwroot ==...

Help me with these 'afxv_w32.h' errors
Hi, I am building a static .lib project in VS 2003.Net The errors I am getting are in file afxv_w32.h as:- C:\Program Files\Microsoft Visual Studio .NET 2003\Vc7\atlmfc\include\afxv_w32.h(230): error C2065: 'DWORD_PTR' : undeclared identifier C:\Program Files\Microsoft Visual Studio .NET 2003\Vc7\atlmfc\include\afxv_w32.h(230): error C2143: syntax error : missing ';' before 'GetWindowThreadProcessId' C:\Program Files\Microsoft Visual Studio .NET 2003\Vc7\atlmfc\include\atlalloc.h(344): error C3861: '_resetstkoflw': identifier not found, even with argument-dep...

MOIBF for Microsoft CRM, client install error 28001
I've not been able to get the client installer to work. The reported error (on installation) is 28001. Setup is unable to retreive the URL for the smart tag refresh from the information bridge metadata service ...

form doesn't show in code drop-down
I have a form with several subforms in code, when I type " me. ", I get a drop down with all of the controls on the form -well actually, it seems, not all (some of the subforms show, 1 or 2 does not the code I'm wanting to get to is simple... me.sfrmFormNameGoes Here.requery any idea what is going on? thanks in advance, mark On Apr 4, 12:30 pm, "mark kubicki" <m...@tillotsondesign.com> wrote: > I have a form with several subforms > > in code, when I type " me. ", I get a drop down with all of the controls on > the form -well act...