code error - memory error

R

Rachel Curran

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
 
P

Pierre

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...
 
D

Dave Peterson

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("&#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 said:
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:
Click to expand...
<<snipped>>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Code runs out of memory - too much copy/paste? 0
issue copying data 2
Copy & paste macro for multiple worksheets 8
Macro runtime 1004 error on opening worksheet 5
Macro- Excel 2003 3
Macro copy and paste = blank worksheet 1
Set up Macro *PLEASE HELP IMPORTANT* 1
Macro - very slow run in 2003 3

Top