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:="£", Replacement:="�", _ SearchOrder:=xlByColumns, MatchCase:=True Worksheets("Todays HRCN's").Columns("A:ez").Replace _ What:="'", Replacement:="'", _ SearchOrder:=xlByColumns, MatchCase:=True Worksheets("Todays HRCN's").Columns("A:ez").Replace _ What:=""", 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
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/
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:="£", Replacement:="�", _ SearchOrder:=xlByColumns, MatchCase:=True Worksheets("Todays HRCN's").Columns("A:ez").Replace _ What:="'", Replacement:="'", _ SearchOrder:=xlByColumns, MatchCase:=True Worksheets("Todays HRCN's").Columns("A:ez").Replace _ What:=""", 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("£", "'", """, "#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