VBA Code runs out of memory - too much copy/paste?

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
 

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

code error - memory error 2
Help me4 2
Pasting Variable cell info into a macro 1
Activate macro in another book 2
Copying filtered data 0
Different Results from the Same Macro 3
replace copy and paste 1
VBA Filter 2

Top