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
6/1/2004 9:40:49 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1046 Views

Similar Articles

[PageSpeed] 32

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
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 tendering transactions
I have 4 tender types set up in RMS: cash, cheque, on account, and COD (which in effect is also on account). Recently I tried to tender a transaction COD, but got an Error 315 message, stating: "A tender type is invalid while in offline mode". BUT, the POS station is NOT working in offline mode. How do I fix this? Is the only problem with the COD tender type? If so you might want to try to delete it and recreate it again. Daniel NuRol Point of Sale and Barcoding www.nurol.com 404-352-3587 >-----Original Message----- >I have 4 tender types set up in RMS: cash, cheque, ...

Error #1004
I use epace which is set up on excel and has many links. When I go into any of my excel workbooks and try refresh the file, I get the following message: Error #1004 was generated by Microsoft Excel Calculate Method of Range class failed. The file saves my trial balance numbers but will not save other changes made (titles and other alpha changes). Any thoughts? ...

Integrate Paycodes, benefit codes and deduction codes
Has anyone used integration manager to update new pay rates, deduction amounts and benefit amounts for employees? At the beginning of each year, our company gives pay increases and we need to update the pay codes, deduction codes and benefit codes for 40 employees, which we get the information from a spreadsheet. I thought that maybe I could use integration manager to update the pay, benefit and deduction codes instead of going into each employee's card, which is time consuming. Thanks, Laura Integration Manager will allow you to do this. Use the Payroll Master Destination. one ...

zip codes don't merge #2
I am trying to mail merge w/ Word 2000 the names and addresses in my worksheet. When I get to the part to choose the format for the mailing labels, I choose F1, F2, etc. to F6 (which is the zip code column). A few do get there, but the vast majority stop at the state, leaving off the entire zip code. I have gone to menu/format and selected text in the number tab. I have gone to format/cells and chosen special/zip code in the number tab. I've read Excel for Dummies. Please help me. TIA bb ...

OWA-"!an unknown error has happened"
In Outlook Web Access, when I try to reply to or forward an e-mail message, I get an error message with a red explanation point in front of it saying "!an unknown error happened." Anyone know what this is? It's also happening at a time when my signal from my cable modem connection is not very strong. Could this be causing it? Try asking this in an Exchange group as OWA is a function of Exchange, not Outlook. microsoft.public.exchange.admin is a good place to start. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to ...

Error 8213
Hi: I have been getting this error for few months. Microsoft suggestion is to reset pasword in System Manager. I honestly cant find where in System Manager to change password. Plus i cannot find the error I am getting in event log (0xc007052e) in MSKB. We use to have a connector to Exchabge 5.5 Server which has been disconnected for a while. Event Type: Error Event Source: MSExchangeFBPublish Event Category: General Event ID: 8213 Date: 7/6/2006 Time: 12:28:28 PM User: N/A Computer: XXX Description: System Attendant Service failed to create session for virtual machine XXX. The...

concatenating two vendor codes
My company recently changed the vendor codes and now I end up with two separate sets of data for each vendor. Obviously I could just leave the vendor code field out. I don't want to do that so what I need to do is concatenate the two codes. Example Vendor Code Vendor Name 123 Joe's Supplies ABC Joe's Supplies what I need is this Vendor Code Vendor Name 123/ABC Joe's Supplies any ideas? Rather than create a new record for each vendor, add another field to the table -- call it NewVendorCode -- and put th...

How do i stop the error message
Does anyone know how to stop the below error mesage on MS Outlook. "The Customer form could not be opened. Outlook will use an oulook form instead. The object could not be found," ...

Login Error #3
I have upgraded my server from 9 to 10; however, I'm working on client install and getting the following error in GP_LoginErrors.log Product Name: HITB Report Database Version: 1001296 Client Version: 9015 I have copied the HTB5597.dic file from server. It also shows version 9015 in the Windows properties, but runs fine on the server. I've copared dynsmics.set files and every seems to be the same. What am I missing? Thanks, G.A. maybe your dictionary is correct, but on the dynamics table in sql it has not upgraded its version run this query and it will tell you the version ...

Can MS EXCEL remove duplicates and separate by color coded items ?
Can MS EXCEL remove duplicates and separate by color coded items ? I am NOT technical and have just started using MS EXCEL. Can anyone PLEASE HELP me: 1. How can I automatically remove duplicates using EXCEL ? ie the same info input more than once on different lines. Can EXCEL do this ? 2, I have color coded the text in the certain lines in terms of priority. can Excel rearrange the data by color ? If yes, How do I do it ? I think I have EXCEL '97 Thanks for your help in advance. Hi 1. You can extract the unique items to a new list using menu Data > Filter > Advanced fil...

16 bit code
Hello, I have a 16 bit code to maintain. (It is too complex to copile it to 32 bit) so all we are doing is support. Now they want to add a small feature, where I need to copy long file names in the project. I know we can not do long file name copies in vc++ (1.52). So I wrote a program in VC++(6.0) which does directory copy. My questions are 1. How can I call a 32 bit exe (I guess WinExec should work)? 2. How can I make the 16 bit to wait till the 32 bit code completes? 3. Is there any other better way to do this? (other than converting to 32 bit) Thanks. I actually do this in an old 16-bi...

Save Error
I have a file that I am trying to save. Everytime I select Save I get the following message: Excel could not save all the data and formatting you recently added to file data file.xls. -To return to your workbook without saving click Cancel. -If you want to continue saving changes without repairing the problem, click OK. -For information about repairing the problem, click Help. When I click Help I get a blank grey box. How can I find out what is wrong? Thanks, JC It kind of sounds like you're getting hit with the fact that Excel has a limit of 4000 different formats in a workbook....

area codes is auto filling my own 9 digit ph# vs just area code
When I enter a phone number for a contact it autofills with my personal area code and phone number instead of just the area code. How to I change this to just autofil the area code? I am using Outlook 2007 on an ACER laptop. Make sure you have your area code entered correctly in "Dialing Properties". "Computer Dummy" wrote: > When I enter a phone number for a contact it autofills with my personal area > code and phone number instead of just the area code. How to I change this to > just autofil the area code? I am using Outlook 2007 on an ACER ...

VBA Code for Pasting Sheets
I would like a spreadhseet that pastes the contents of one sheet into another sheet. I like like to do this for 7 different sheets For example: I would like paste the contents form sheet titled "sheet1" into a sheet titled "data1". Continue to process for pasting "sheet2" into "data2" and "sheet3" into "data3" all way until "sheet7" and "data7". thanks, Curt Subject: Automated Copy Paste Subject: Copy/Paste Import/Export Data VBA Code On Apr 27, 10:49=A0am, Curt <C...@discussions.mi...

MC/VISA joint code
Isn't there any way to combine the blocks (4* & 5*) for MC/VISA credit cards? It's a pain to have them separate because my bank posts them together. It is the same company afterall. It would save me a lot of time adding separate entries together to reconcile the statement! I do not know of a way to do what you are asking, but I have found that using the # symbol as follows works better than using the * symbol: 4############### 5############### If you use the * symbol it is a wild card with no determined length. The # symbol requires that the characters after the first d...

Error message when opening attachments
I receive the following error when trying to open mail attachments: OUTLOOK.EXE szAppVer: 10.06626.0 szModName:hungapp szModVer:0.0.0.0 I use the install disc to "repair" MS - but error persists. Can anyone assist me? Hi Robert, This issue can occur if the Outlook cache files are corrupt. Please perform the steps below to rename and recreate them: Step1: Rename Cache files =============== Perform a search on your computer to find the following files: extend.dat outcmd.dat frmcache.dat views.dat NOTE: Some of them may not exist, but it should not matter. You may also need ...

Workflow Manager won't start, error saying CRM server not availabl
I have been trying to work with the CRM workflow Manager, so when it is opening it show me a message " The specified Microsoft CRM server is not responding. This Might happen if it is currently unavailable or it is not a Microsoft CRM server.", I check the services and everithing looks fine. I searched this newsgroup and saw back in September someone else reported same problem and the response was a licensing issue. I confirm that I have Professional Suite (purchased via the Partner action Pack) so I don't think that is my problem. I can access crm fine, just get this wh...

Money 2004 - Experian Offer
I just loaded Money 2004 Deluxe and converted my 2003 files with no problem. I decided to give the free year of credit monitoring from Experian a try but after filling everything out I get an error that the provided code has expired. I neither had nor provided any code for this offer, I only used the link from Money 2004. Now Experian was more than willing to set up a $79 account if I wanted to proceed. Any solution out there? I have the same problem... I am looking for a FREE way to contact Microsoft about this problem. >-----Original Message----- >I just loaded Money 2004 ...

Analytical Accounting
Hi, One of my Client's is considering to go down AA path with approx 300,000 AA dimension codes. I would like to hear whether such data set is manageable from Query wizard etc. appreciate your thoughts. Good Morning PR, I found that the issue isn't the number of AA Trx Dim Codes it is the size of the AAG30000 and AAG40000 tables the more entries the longer it takes to product the report. I had one client that started having a cached SSRS report with AA information as a management level report and a different SSRS report with a more restrictive dataset for reporting ...

Change code with code?
Hi All..... I've got 31 Excel .xlsm files to modify the "Change Event" macro in. Is it possible to open and perform this with code, or must I do each one by hand? TIA Vaya con Dios, Chuck, CABGx3 If it is the same change in each sheet, it would probably be just as easy to go into the VBE, double click on the first sheet, make the correction, then copy that correction and double click the second sheet, delete and paste. Repeat the process 30 times. Takes less time than writing the code to do it. "CLR" <CLR@discussions.microsoft.com> wr...

Access 2003
I have a form that goes to a subform for searching. It works perfectly on my computer; however, when another user opens it and attempts to perform a search, they receive the Error Code 2455 message. When I debug, it gets stuck on "Me.book_subform.Form.Filter = strWhereSearch" and says that book_subform cannot be found. It isn't a rights issue, it isn't a mapping issue. What am I missing? check your naming. This is an issue that often confused. In this syntax: Me.book_subform.Form.Filter > = strWhereSearch book_subform is the name of the subform control on the main ...

Synchronization errors
Does all the clients need to be online while customizatins are done, For example, if I remove field from CRM database, and users have transferred it to offline mode. When users come back onlin there is a synchronization errors. Errors are Finnish so I dont know what they are in english, but thing is that synchronization freezes and doesn´t go through.... Yours, Mikko ...

Write a code by code
How can I write a code from procedure to some Workbook's ThisWorkbook? With procedure1 a make Workbook with a table. I need to write Auto_Close procedure to ThisWorkbook which makes some controls before I close it. Is it possible? <jenista.j@discussions.microsoft.com> wrote in message news:0be501c52fab$ba0b6830$a601280a@phx.gbl... > How can I write a code from procedure to some Workbook's > ThisWorkbook? With procedure1 a make Workbook with a table. > I need to write Auto_Close procedure to ThisWorkbook which > makes some controls before I close it. Is it possible? ...

Coding Duplicate Records
How can I code that a record is to be "K" kept or "D" deleted when compared to its next row? Example: A sorted file by Address BEFORE LastName Address Sales Date Keep/Delete 1. WASSEM 0N642 TITUS PL 67,500 6/25/2001 2. HART 0N655 E WEAVER CIR 70,000 3/19/2001 3. HART 0N655 E WEAVER CIR 40,000 3/19/2001 4. HART 0N655 WEAVER CIRCLE 33,700 6/19/1998 5. POYTH 0N662 W WEAVER CIR 294,690 6/21/2000 6. BLAND 0N670 GREEN PL ...

Conversion error...
I'm upgrading from Money 2003 to 2005. When 2005 starts it says it's converting the file. It makes the back up copy but then hangs for a long time (5-10 minutes) then comes up with an error... " A communication error occurred. Please try your call again. If you continue to have problems call your financial institutions Online Services Customer Support" If I revert to 2003 everything works as it should. The knowledge base mentions this error message. It says the fix is to search for a file *.m14 and turn off MSN bill pay. A search doesn't produce any file that...