code error - memory error
Please can anyone help - my PC runs out of memory (I currently have
512) I'm presuming that my code is badly written - can anyone please
help with this, code follows:
Private Sub CommandButton1_Click()
' Option box allowing user to select relevant ctr
filetoopen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If filetoopen <> False Then
Workbooks.Open Filename:= _
filetoopen
End If
Dim strPath As String, strFileName As String
strPath = ActiveWorkbook.FullName
strFileName = Mid(strPath, InStrRev(strPath, "\") + 1)
' manipulating ctr data
Workbooks(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("D:D").Select
Selection.Copy
Workbooks("HRCN_EXT_DATA1test2.xls").Activate
Sheets("CTR_DATA").Select
Columns("A:A").Select
ActiveSheet.Paste
Workbooks(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("F:F").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Columns("B:B").Select
ActiveSheet.Paste
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("G:H").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("C1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("J:L").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("E1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("M:O").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("H1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
ActiveWindow.SmallScroll ToRight:=8
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("Q:S").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("K1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=8
Windows(strFileName).Activate
ActiveWindow.SmallScroll ToRight:=5
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("Y:Y").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("N1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows(strFileName).Activate
ActiveWindow.SmallScroll ToRight:=5
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("AC:AC").Select
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("O1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("P1").Select
Windows(strFileName).Activate
ActiveWindow.SmallScroll ToRight:=14
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("AH:AH").ColumnWidth =
13.29
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("AH:AH").Select
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("AP:AQ").Select
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Application.CutCopyMode = False
Range("Q1").Select
Windows(strFileName).Activate
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("AH:AH").Select
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("P1").Select
ActiveSheet.Paste
Range("S1").Select
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("AS:AT").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=9
Windows(strFileName).Activate
ActiveWindow.SmallScroll ToRight:=9
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("BB:BC").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("U1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
ActiveWindow.SmallScroll ToRight:=11
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("BL:BL").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("W1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("BN:BN").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("X1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
ActiveWindow.SmallScroll ToRight:=6
Windows("HRCN_EXT_DATA1test2.xls").Activate
ActiveWindow.SmallScroll ToRight:=4
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("BO:BR").Select
Windows("HRCN_EXT_DATA1test2.xls").Activate
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("BO:BS").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("Y1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("BT:BU").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("AD1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=5
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("BX:BX").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("AF1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Range("CA1").Select
Windows("HRCN_EXT_DATA1test2.xls").Activate
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("CA:CA").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("AG1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Range("CF1").Select
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Worksheets("CTR_DATA").Columns("AH:AH").Select
ActiveSheet.Paste
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Range("CG1").Select
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Worksheets("CTR_DATA").Columns("AI:AI").Select
ActiveSheet.Paste
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("CF:CG").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
ActiveSheet.Paste
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("CJ:CM").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("AJ1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("CN:CP").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("AN1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Range("ET1").Select
Selection.Copy
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("ES:EV").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("AQ1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=6
Windows(strFileName).Activate
ActiveWindow.SmallScroll ToRight:=9
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("EW:EY").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("AU1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=6
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("EZ:FB").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("AX1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("FC:FD").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("BA1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("FI:FJ").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("BC1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Range("FL1").Select
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("FK:FK").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("BE1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
ActiveWindow.SmallScroll ToRight:=7
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("FQ:FQ").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("BF1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
ActiveWindow.SmallScroll ToRight:=4
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("FU:FU").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("BG1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("FW:FW").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("BH1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
ActiveWindow.SmallScroll ToRight:=7
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("FY:FY").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("BI1").Select
ActiveSheet.Paste
Windows(strFileName).Activate
Worksheets("PUBLIC_XXHRS_PERSON_V").Columns("GA:GA").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HRCN_EXT_DATA1test2.xls").Activate
Range("BJ1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Calculate
Sheets("CTR_DATA").Select
Range("a1").Activate
'Saves worksheet
ActiveWorkbook.Save
'closes workbook ctr without saving
Workbooks(strFileName).Close (False)
'Deletes all data in Todays HRCN tab
Workbooks("HRCN_EXT_DATA1test2.xls").Activate
Sheets("Todays HRCN's").Select
Application.CutCopyMode = False
Selection.ClearContents
'Copy formulas in workings tab down to 5000 line
Workbooks("HRCN_EXT_DATA1test2.xls").Activate
Worksheets("Workings").Select
Worksheets("Workings").Range("A2.BM2").Cells.Select
Selection.AutoFill
Destination:=Worksheets("Workings").Range("A2.BM5000"),
Type:=xlFillDefault
Worksheets("Workings").Range("A2.BM5000").Cells.Select
Worksheets("Workings").Calculate
'Copy workings tab
Workbooks("HRCN_EXT_DATA1test2.xls").Activate
Sheets("Workings").Cells.Copy
' Paste values to Todays HRCN tab
Sheets("Todays HRCN's").Range("A1").PasteSpecial _
Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Todays HRCN's").Range("A1").PasteSpecial _
Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'Replaces every occurrence of the #N/A, REF! etc
'with the relevant symbol.
Worksheets("Todays HRCN's").Columns("A:ez").Replace _
What:="£", 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
curran_rachel
excel.misc
78881 articles. 
5 followers.
2 Replies
1128 Views
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/
Pierre.1761rfYou 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
ec35720
Similar Artilces:
Error tendering transactions
I have 4 tender types set up in RMS: cash, cheque, on account, and COD (which in effect is also on account). Recently I tried to tender a transaction COD, but got an Error 315 message, stating: "A tender type is invalid while in offline mode". BUT, the POS station is NOT working in offline mode. How do I fix this? Is the only problem with the COD tender type? If so you might want to try to delete it and recreate it again. Daniel NuRol Point of Sale and Barcoding www.nurol.com 404-352-3587 >-----Original Message----- >I have 4 tender types set up in RMS: cash, cheque, ...
Error #1004
I use epace which is set up on excel and has many links. When I go into any of my excel workbooks and try refresh the file, I get the following message: Error #1004 was generated by Microsoft Excel Calculate Method of Range class failed. The file saves my trial balance numbers but will not save other changes made (titles and other alpha changes). Any thoughts? ...
Integrate Paycodes, benefit codes and deduction codes
Has anyone used integration manager to update new pay rates, deduction amounts and benefit amounts for employees? At the beginning of each year, our company gives pay increases and we need to update the pay codes, deduction codes and benefit codes for 40 employees, which we get the information from a spreadsheet. I thought that maybe I could use integration manager to update the pay, benefit and deduction codes instead of going into each employee's card, which is time consuming. Thanks, Laura Integration Manager will allow you to do this. Use the Payroll Master Destination. one ...
zip codes don't merge #2
I am trying to mail merge w/ Word 2000 the names and addresses in my worksheet. When I get to the part to choose the format for the mailing labels, I choose F1, F2, etc. to F6 (which is the zip code column). A few do get there, but the vast majority stop at the state, leaving off the entire zip code. I have gone to menu/format and selected text in the number tab. I have gone to format/cells and chosen special/zip code in the number tab. I've read Excel for Dummies. Please help me. TIA bb ...
OWA-"!an unknown error has happened"
In Outlook Web Access, when I try to reply to or forward an e-mail message, I get an error message with a red explanation point in front of it saying "!an unknown error happened." Anyone know what this is? It's also happening at a time when my signal from my cable modem connection is not very strong. Could this be causing it? Try asking this in an Exchange group as OWA is a function of Exchange, not Outlook. microsoft.public.exchange.admin is a good place to start. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to ...
Error 8213
Hi: I have been getting this error for few months. Microsoft suggestion is to reset pasword in System Manager. I honestly cant find where in System Manager to change password. Plus i cannot find the error I am getting in event log (0xc007052e) in MSKB. We use to have a connector to Exchabge 5.5 Server which has been disconnected for a while. Event Type: Error Event Source: MSExchangeFBPublish Event Category: General Event ID: 8213 Date: 7/6/2006 Time: 12:28:28 PM User: N/A Computer: XXX Description: System Attendant Service failed to create session for virtual machine XXX. The...
concatenating two vendor codes
My company recently changed the vendor codes and now I end up with two separate sets of data for each vendor. Obviously I could just leave the vendor code field out. I don't want to do that so what I need to do is concatenate the two codes. Example Vendor Code Vendor Name 123 Joe's Supplies ABC Joe's Supplies what I need is this Vendor Code Vendor Name 123/ABC Joe's Supplies any ideas? Rather than create a new record for each vendor, add another field to the table -- call it NewVendorCode -- and put th...
How do i stop the error message
Does anyone know how to stop the below error mesage on MS Outlook. "The Customer form could not be opened. Outlook will use an oulook form instead. The object could not be found," ...
Login Error #3
I have upgraded my server from 9 to 10; however, I'm working on client install and getting the following error in GP_LoginErrors.log Product Name: HITB Report Database Version: 1001296 Client Version: 9015 I have copied the HTB5597.dic file from server. It also shows version 9015 in the Windows properties, but runs fine on the server. I've copared dynsmics.set files and every seems to be the same. What am I missing? Thanks, G.A. maybe your dictionary is correct, but on the dynamics table in sql it has not upgraded its version run this query and it will tell you the version ...
Can MS EXCEL remove duplicates and separate by color coded items ?
Can MS EXCEL remove duplicates and separate by color coded items ? I am NOT technical and have just started using MS EXCEL. Can anyone PLEASE HELP me: 1. How can I automatically remove duplicates using EXCEL ? ie the same info input more than once on different lines. Can EXCEL do this ? 2, I have color coded the text in the certain lines in terms of priority. can Excel rearrange the data by color ? If yes, How do I do it ? I think I have EXCEL '97 Thanks for your help in advance. Hi 1. You can extract the unique items to a new list using menu Data > Filter > Advanced fil...
16 bit code
Hello, I have a 16 bit code to maintain. (It is too complex to copile it to 32 bit) so all we are doing is support. Now they want to add a small feature, where I need to copy long file names in the project. I know we can not do long file name copies in vc++ (1.52). So I wrote a program in VC++(6.0) which does directory copy. My questions are 1. How can I call a 32 bit exe (I guess WinExec should work)? 2. How can I make the 16 bit to wait till the 32 bit code completes? 3. Is there any other better way to do this? (other than converting to 32 bit) Thanks. I actually do this in an old 16-bi...
Save Error
I have a file that I am trying to save. Everytime I select Save I get the following message: Excel could not save all the data and formatting you recently added to file data file.xls. -To return to your workbook without saving click Cancel. -If you want to continue saving changes without repairing the problem, click OK. -For information about repairing the problem, click Help. When I click Help I get a blank grey box. How can I find out what is wrong? Thanks, JC It kind of sounds like you're getting hit with the fact that Excel has a limit of 4000 different formats in a workbook....
area codes is auto filling my own 9 digit ph# vs just area code
When I enter a phone number for a contact it autofills with my personal area code and phone number instead of just the area code. How to I change this to just autofil the area code? I am using Outlook 2007 on an ACER laptop. Make sure you have your area code entered correctly in "Dialing Properties". "Computer Dummy" wrote: > When I enter a phone number for a contact it autofills with my personal area > code and phone number instead of just the area code. How to I change this to > just autofil the area code? I am using Outlook 2007 on an ACER ...
VBA Code for Pasting Sheets
I would like a spreadhseet that pastes the contents of one sheet into another sheet. I like like to do this for 7 different sheets For example: I would like paste the contents form sheet titled "sheet1" into a sheet titled "data1". Continue to process for pasting "sheet2" into "data2" and "sheet3" into "data3" all way until "sheet7" and "data7". thanks, Curt Subject: Automated Copy Paste Subject: Copy/Paste Import/Export Data VBA Code On Apr 27, 10:49=A0am, Curt <C...@discussions.mi...
MC/VISA joint code
Isn't there any way to combine the blocks (4* & 5*) for MC/VISA credit cards? It's a pain to have them separate because my bank posts them together. It is the same company afterall. It would save me a lot of time adding separate entries together to reconcile the statement! I do not know of a way to do what you are asking, but I have found that using the # symbol as follows works better than using the * symbol: 4############### 5############### If you use the * symbol it is a wild card with no determined length. The # symbol requires that the characters after the first d...
Error message when opening attachments
I receive the following error when trying to open mail attachments: OUTLOOK.EXE szAppVer: 10.06626.0 szModName:hungapp szModVer:0.0.0.0 I use the install disc to "repair" MS - but error persists. Can anyone assist me? Hi Robert, This issue can occur if the Outlook cache files are corrupt. Please perform the steps below to rename and recreate them: Step1: Rename Cache files =============== Perform a search on your computer to find the following files: extend.dat outcmd.dat frmcache.dat views.dat NOTE: Some of them may not exist, but it should not matter. You may also need ...
Workflow Manager won't start, error saying CRM server not availabl
I have been trying to work with the CRM workflow Manager, so when it is opening it show me a message " The specified Microsoft CRM server is not responding. This Might happen if it is currently unavailable or it is not a Microsoft CRM server.", I check the services and everithing looks fine. I searched this newsgroup and saw back in September someone else reported same problem and the response was a licensing issue. I confirm that I have Professional Suite (purchased via the Partner action Pack) so I don't think that is my problem. I can access crm fine, just get this wh...
Money 2004 - Experian Offer
I just loaded Money 2004 Deluxe and converted my 2003 files with no problem. I decided to give the free year of credit monitoring from Experian a try but after filling everything out I get an error that the provided code has expired. I neither had nor provided any code for this offer, I only used the link from Money 2004. Now Experian was more than willing to set up a $79 account if I wanted to proceed. Any solution out there? I have the same problem... I am looking for a FREE way to contact Microsoft about this problem. >-----Original Message----- >I just loaded Money 2004 ...
Analytical Accounting
Hi, One of my Client's is considering to go down AA path with approx 300,000 AA dimension codes. I would like to hear whether such data set is manageable from Query wizard etc. appreciate your thoughts. Good Morning PR, I found that the issue isn't the number of AA Trx Dim Codes it is the size of the AAG30000 and AAG40000 tables the more entries the longer it takes to product the report. I had one client that started having a cached SSRS report with AA information as a management level report and a different SSRS report with a more restrictive dataset for reporting ...
Change code with code?
Hi All..... I've got 31 Excel .xlsm files to modify the "Change Event" macro in. Is it possible to open and perform this with code, or must I do each one by hand? TIA Vaya con Dios, Chuck, CABGx3 If it is the same change in each sheet, it would probably be just as easy to go into the VBE, double click on the first sheet, make the correction, then copy that correction and double click the second sheet, delete and paste. Repeat the process 30 times. Takes less time than writing the code to do it. "CLR" <CLR@discussions.microsoft.com> wr...
Access 2003
I have a form that goes to a subform for searching. It works perfectly on my computer; however, when another user opens it and attempts to perform a search, they receive the Error Code 2455 message. When I debug, it gets stuck on "Me.book_subform.Form.Filter = strWhereSearch" and says that book_subform cannot be found. It isn't a rights issue, it isn't a mapping issue. What am I missing? check your naming. This is an issue that often confused. In this syntax: Me.book_subform.Form.Filter > = strWhereSearch book_subform is the name of the subform control on the main ...
Synchronization errors
Does all the clients need to be online while customizatins are done, For example, if I remove field from CRM database, and users have transferred it to offline mode. When users come back onlin there is a synchronization errors. Errors are Finnish so I dont know what they are in english, but thing is that synchronization freezes and doesn´t go through.... Yours, Mikko ...
Write a code by code
How can I write a code from procedure to some Workbook's ThisWorkbook? With procedure1 a make Workbook with a table. I need to write Auto_Close procedure to ThisWorkbook which makes some controls before I close it. Is it possible? <jenista.j@discussions.microsoft.com> wrote in message news:0be501c52fab$ba0b6830$a601280a@phx.gbl... > How can I write a code from procedure to some Workbook's > ThisWorkbook? With procedure1 a make Workbook with a table. > I need to write Auto_Close procedure to ThisWorkbook which > makes some controls before I close it. Is it possible? ...
Coding Duplicate Records
How can I code that a record is to be "K" kept or "D" deleted when compared to its next row? Example: A sorted file by Address BEFORE LastName Address Sales Date Keep/Delete 1. WASSEM 0N642 TITUS PL 67,500 6/25/2001 2. HART 0N655 E WEAVER CIR 70,000 3/19/2001 3. HART 0N655 E WEAVER CIR 40,000 3/19/2001 4. HART 0N655 WEAVER CIRCLE 33,700 6/19/1998 5. POYTH 0N662 W WEAVER CIR 294,690 6/21/2000 6. BLAND 0N670 GREEN PL ...
Conversion error...
I'm upgrading from Money 2003 to 2005. When 2005 starts it says it's converting the file. It makes the back up copy but then hangs for a long time (5-10 minutes) then comes up with an error... " A communication error occurred. Please try your call again. If you continue to have problems call your financial institutions Online Services Customer Support" If I revert to 2003 everything works as it should. The knowledge base mentions this error message. It says the fix is to search for a file *.m14 and turn off MSN bill pay. A search doesn't produce any file that...
I have 4 tender types set up in RMS: cash, cheque, on account, and COD (which in effect is also on account). Recently I tried to tender a transaction COD, but got an Error 315 message, stating: "A tender type is invalid while in offline mode". BUT, the POS station is NOT working in offline mode. How do I fix this? Is the only problem with the COD tender type? If so you might want to try to delete it and recreate it again. Daniel NuRol Point of Sale and Barcoding www.nurol.com 404-352-3587 >-----Original Message----- >I have 4 tender types set up in RMS: cash, cheque, ...
Error #1004
I use epace which is set up on excel and has many links. When I go into any of my excel workbooks and try refresh the file, I get the following message: Error #1004 was generated by Microsoft Excel Calculate Method of Range class failed. The file saves my trial balance numbers but will not save other changes made (titles and other alpha changes). Any thoughts? ...
Integrate Paycodes, benefit codes and deduction codes
Has anyone used integration manager to update new pay rates, deduction amounts and benefit amounts for employees? At the beginning of each year, our company gives pay increases and we need to update the pay codes, deduction codes and benefit codes for 40 employees, which we get the information from a spreadsheet. I thought that maybe I could use integration manager to update the pay, benefit and deduction codes instead of going into each employee's card, which is time consuming. Thanks, Laura Integration Manager will allow you to do this. Use the Payroll Master Destination. one ...
zip codes don't merge #2
I am trying to mail merge w/ Word 2000 the names and addresses in my worksheet. When I get to the part to choose the format for the mailing labels, I choose F1, F2, etc. to F6 (which is the zip code column). A few do get there, but the vast majority stop at the state, leaving off the entire zip code. I have gone to menu/format and selected text in the number tab. I have gone to format/cells and chosen special/zip code in the number tab. I've read Excel for Dummies. Please help me. TIA bb ...
OWA-"!an unknown error has happened"
In Outlook Web Access, when I try to reply to or forward an e-mail message, I get an error message with a red explanation point in front of it saying "!an unknown error happened." Anyone know what this is? It's also happening at a time when my signal from my cable modem connection is not very strong. Could this be causing it? Try asking this in an Exchange group as OWA is a function of Exchange, not Outlook. microsoft.public.exchange.admin is a good place to start. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to ...
Error 8213
Hi: I have been getting this error for few months. Microsoft suggestion is to reset pasword in System Manager. I honestly cant find where in System Manager to change password. Plus i cannot find the error I am getting in event log (0xc007052e) in MSKB. We use to have a connector to Exchabge 5.5 Server which has been disconnected for a while. Event Type: Error Event Source: MSExchangeFBPublish Event Category: General Event ID: 8213 Date: 7/6/2006 Time: 12:28:28 PM User: N/A Computer: XXX Description: System Attendant Service failed to create session for virtual machine XXX. The...
concatenating two vendor codes
My company recently changed the vendor codes and now I end up with two separate sets of data for each vendor. Obviously I could just leave the vendor code field out. I don't want to do that so what I need to do is concatenate the two codes. Example Vendor Code Vendor Name 123 Joe's Supplies ABC Joe's Supplies what I need is this Vendor Code Vendor Name 123/ABC Joe's Supplies any ideas? Rather than create a new record for each vendor, add another field to the table -- call it NewVendorCode -- and put th...
How do i stop the error message
Does anyone know how to stop the below error mesage on MS Outlook. "The Customer form could not be opened. Outlook will use an oulook form instead. The object could not be found," ...
Login Error #3
I have upgraded my server from 9 to 10; however, I'm working on client install and getting the following error in GP_LoginErrors.log Product Name: HITB Report Database Version: 1001296 Client Version: 9015 I have copied the HTB5597.dic file from server. It also shows version 9015 in the Windows properties, but runs fine on the server. I've copared dynsmics.set files and every seems to be the same. What am I missing? Thanks, G.A. maybe your dictionary is correct, but on the dynamics table in sql it has not upgraded its version run this query and it will tell you the version ...
Can MS EXCEL remove duplicates and separate by color coded items ?
Can MS EXCEL remove duplicates and separate by color coded items ? I am NOT technical and have just started using MS EXCEL. Can anyone PLEASE HELP me: 1. How can I automatically remove duplicates using EXCEL ? ie the same info input more than once on different lines. Can EXCEL do this ? 2, I have color coded the text in the certain lines in terms of priority. can Excel rearrange the data by color ? If yes, How do I do it ? I think I have EXCEL '97 Thanks for your help in advance. Hi 1. You can extract the unique items to a new list using menu Data > Filter > Advanced fil...
16 bit code
Hello, I have a 16 bit code to maintain. (It is too complex to copile it to 32 bit) so all we are doing is support. Now they want to add a small feature, where I need to copy long file names in the project. I know we can not do long file name copies in vc++ (1.52). So I wrote a program in VC++(6.0) which does directory copy. My questions are 1. How can I call a 32 bit exe (I guess WinExec should work)? 2. How can I make the 16 bit to wait till the 32 bit code completes? 3. Is there any other better way to do this? (other than converting to 32 bit) Thanks. I actually do this in an old 16-bi...
Save Error
I have a file that I am trying to save. Everytime I select Save I get the following message: Excel could not save all the data and formatting you recently added to file data file.xls. -To return to your workbook without saving click Cancel. -If you want to continue saving changes without repairing the problem, click OK. -For information about repairing the problem, click Help. When I click Help I get a blank grey box. How can I find out what is wrong? Thanks, JC It kind of sounds like you're getting hit with the fact that Excel has a limit of 4000 different formats in a workbook....
area codes is auto filling my own 9 digit ph# vs just area code
When I enter a phone number for a contact it autofills with my personal area code and phone number instead of just the area code. How to I change this to just autofil the area code? I am using Outlook 2007 on an ACER laptop. Make sure you have your area code entered correctly in "Dialing Properties". "Computer Dummy" wrote: > When I enter a phone number for a contact it autofills with my personal area > code and phone number instead of just the area code. How to I change this to > just autofil the area code? I am using Outlook 2007 on an ACER ...
VBA Code for Pasting Sheets
I would like a spreadhseet that pastes the contents of one sheet into another sheet. I like like to do this for 7 different sheets For example: I would like paste the contents form sheet titled "sheet1" into a sheet titled "data1". Continue to process for pasting "sheet2" into "data2" and "sheet3" into "data3" all way until "sheet7" and "data7". thanks, Curt Subject: Automated Copy Paste Subject: Copy/Paste Import/Export Data VBA Code On Apr 27, 10:49=A0am, Curt <C...@discussions.mi...
MC/VISA joint code
Isn't there any way to combine the blocks (4* & 5*) for MC/VISA credit cards? It's a pain to have them separate because my bank posts them together. It is the same company afterall. It would save me a lot of time adding separate entries together to reconcile the statement! I do not know of a way to do what you are asking, but I have found that using the # symbol as follows works better than using the * symbol: 4############### 5############### If you use the * symbol it is a wild card with no determined length. The # symbol requires that the characters after the first d...
Error message when opening attachments
I receive the following error when trying to open mail attachments: OUTLOOK.EXE szAppVer: 10.06626.0 szModName:hungapp szModVer:0.0.0.0 I use the install disc to "repair" MS - but error persists. Can anyone assist me? Hi Robert, This issue can occur if the Outlook cache files are corrupt. Please perform the steps below to rename and recreate them: Step1: Rename Cache files =============== Perform a search on your computer to find the following files: extend.dat outcmd.dat frmcache.dat views.dat NOTE: Some of them may not exist, but it should not matter. You may also need ...
Workflow Manager won't start, error saying CRM server not availabl
I have been trying to work with the CRM workflow Manager, so when it is opening it show me a message " The specified Microsoft CRM server is not responding. This Might happen if it is currently unavailable or it is not a Microsoft CRM server.", I check the services and everithing looks fine. I searched this newsgroup and saw back in September someone else reported same problem and the response was a licensing issue. I confirm that I have Professional Suite (purchased via the Partner action Pack) so I don't think that is my problem. I can access crm fine, just get this wh...
Money 2004 - Experian Offer
I just loaded Money 2004 Deluxe and converted my 2003 files with no problem. I decided to give the free year of credit monitoring from Experian a try but after filling everything out I get an error that the provided code has expired. I neither had nor provided any code for this offer, I only used the link from Money 2004. Now Experian was more than willing to set up a $79 account if I wanted to proceed. Any solution out there? I have the same problem... I am looking for a FREE way to contact Microsoft about this problem. >-----Original Message----- >I just loaded Money 2004 ...
Analytical Accounting
Hi, One of my Client's is considering to go down AA path with approx 300,000 AA dimension codes. I would like to hear whether such data set is manageable from Query wizard etc. appreciate your thoughts. Good Morning PR, I found that the issue isn't the number of AA Trx Dim Codes it is the size of the AAG30000 and AAG40000 tables the more entries the longer it takes to product the report. I had one client that started having a cached SSRS report with AA information as a management level report and a different SSRS report with a more restrictive dataset for reporting ...
Change code with code?
Hi All..... I've got 31 Excel .xlsm files to modify the "Change Event" macro in. Is it possible to open and perform this with code, or must I do each one by hand? TIA Vaya con Dios, Chuck, CABGx3 If it is the same change in each sheet, it would probably be just as easy to go into the VBE, double click on the first sheet, make the correction, then copy that correction and double click the second sheet, delete and paste. Repeat the process 30 times. Takes less time than writing the code to do it. "CLR" <CLR@discussions.microsoft.com> wr...
Access 2003
I have a form that goes to a subform for searching. It works perfectly on my computer; however, when another user opens it and attempts to perform a search, they receive the Error Code 2455 message. When I debug, it gets stuck on "Me.book_subform.Form.Filter = strWhereSearch" and says that book_subform cannot be found. It isn't a rights issue, it isn't a mapping issue. What am I missing? check your naming. This is an issue that often confused. In this syntax: Me.book_subform.Form.Filter > = strWhereSearch book_subform is the name of the subform control on the main ...
Synchronization errors
Does all the clients need to be online while customizatins are done, For example, if I remove field from CRM database, and users have transferred it to offline mode. When users come back onlin there is a synchronization errors. Errors are Finnish so I dont know what they are in english, but thing is that synchronization freezes and doesn´t go through.... Yours, Mikko ...
Write a code by code
How can I write a code from procedure to some Workbook's ThisWorkbook? With procedure1 a make Workbook with a table. I need to write Auto_Close procedure to ThisWorkbook which makes some controls before I close it. Is it possible? <jenista.j@discussions.microsoft.com> wrote in message news:0be501c52fab$ba0b6830$a601280a@phx.gbl... > How can I write a code from procedure to some Workbook's > ThisWorkbook? With procedure1 a make Workbook with a table. > I need to write Auto_Close procedure to ThisWorkbook which > makes some controls before I close it. Is it possible? ...
Coding Duplicate Records
How can I code that a record is to be "K" kept or "D" deleted when compared to its next row? Example: A sorted file by Address BEFORE LastName Address Sales Date Keep/Delete 1. WASSEM 0N642 TITUS PL 67,500 6/25/2001 2. HART 0N655 E WEAVER CIR 70,000 3/19/2001 3. HART 0N655 E WEAVER CIR 40,000 3/19/2001 4. HART 0N655 WEAVER CIRCLE 33,700 6/19/1998 5. POYTH 0N662 W WEAVER CIR 294,690 6/21/2000 6. BLAND 0N670 GREEN PL ...
Conversion error...
I'm upgrading from Money 2003 to 2005. When 2005 starts it says it's converting the file. It makes the back up copy but then hangs for a long time (5-10 minutes) then comes up with an error... " A communication error occurred. Please try your call again. If you continue to have problems call your financial institutions Online Services Customer Support" If I revert to 2003 everything works as it should. The knowledge base mentions this error message. It says the fix is to search for a file *.m14 and turn off MSN bill pay. A search doesn't produce any file that...