Please help! I've written the following macro and it was working fine until I completely closed my workbook and tried using it again. It now stops and prompts for the location of a file that a formula [=INDEX('[Country Codes recd 022114 (DO NOT DELETE).xlsx] XXXXXCtryLst'!$B$2:$B$5,MATCH(A2,'[Country Codes recd 022114 (DO NOT DELETE).xlsx] XXXXXCtryLst'!$A$2:$A$5,0))] it uses is based on. After I've selected the appropriate file, the macro works fine and will continue to do so until I completely close the workbook again.
Following is the entire macro. Many thanks in advance for any help you can provide. Since I'm very new at this, any advice for shortening this will be greatly appreciated.
Sub APDownload()
'
' Code to edit file downloaded from XXXXXX
'
'
Application.ScreenUpdating = False
Columns("F:F").Select
Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 9), Array(3, 9)), TrailingMinusNumbers:=True
Selection.NumberFormat = "mm/dd/yy;@"
Columns("G:G").Select
Selection.Style = "Currency"
Columns("K:K").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Columns("O:P").Select
Selection.Delete Shift:=xlToLeft
ActiveCell.FormulaR1C1 = "Currency"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Cntry Code"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Vendor"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Vendor #"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Inv. Date"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Inv. #"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Co. #"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Acct. #"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Dimension"
Range("N1").Select
ActiveCell.FormulaR1C1 = "InterCo. #"
Range("O1").Select
Cells.Select
Cells.EntireColumn.AutoFit
Columns("O:P").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(30, 9)), _
TrailingMinusNumbers:=True
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(30, 9)), _
TrailingMinusNumbers:=True
Columns("D:D").EntireColumn.AutoFit
Columns("I:I").Select
Selection.TextToColumns Destination:=Range("I1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(30, 9)), _
TrailingMinusNumbers:=True
Columns("I:I").EntireColumn.AutoFit
Range("A1").Select
Range("B2").Formula = "=INDEX('[Country Codes recd 022114 (DO NOT DELETE).xlsx] XXXXXCtryLst'!$B$2:$B$5,MATCH(A2,'[Country Codes recd 022114 (DO NOT DELETE).xlsx] XXXXXCtryLst'!$A$2:$A$5,0))"
Range("B2").AutoFill Destination:=Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("B:B").Select
Selection.Copy
Columns("C:C").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Range("O1").Select
ActiveCell.FormulaR1C1 = "IC Code"
Range("O1").Select
Range("O2").Formula = "=IF(OR(N2="""",SUMPRODUCT(--ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9,0},N2)))),""Yes"",""No"")"
Range("O2").AutoFill Destination:=Range("O2:O" & Range("N" & Rows.Count).End(xlUp).Row)
Range("O1").Select
Selection.AutoFilter
ActiveWindow.LargeScroll Down:=1
Range("O27").Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range("O1").Select
ActiveSheet.Range("$A$1:$O$67").AutoFilter Field:=15, Criteria1:="No"
Range("N4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("O1").Select
ActiveSheet.Range("$A$1:$O$67").AutoFilter Field:=15
Columns("O:O").Select
Selection.ClearContents
Range("N1").Select
Columns("N:N").EntireColumn.AutoFit
Columns("N:N").Select
With Selection
.HorizontalAlignment = xlLeft
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("M1").Select
Selection.AutoFilter
Selection.End(xlToLeft).Select
Columns("N:N").EntireColumn.AutoFit
Range("A1").Select
Selection.End(xlToLeft).Select
Range("A1").Select
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
Range("K2:K67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
Range("D2:D67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("A1:N67")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
' PgViewFormat Macro
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
ActiveWindow.View = xlNormalView
Range("A1").Select
Application.ScreenUpdating = True
End Sub
Following is the entire macro. Many thanks in advance for any help you can provide. Since I'm very new at this, any advice for shortening this will be greatly appreciated.
Sub APDownload()
'
' Code to edit file downloaded from XXXXXX
'
'
Application.ScreenUpdating = False
Columns("F:F").Select
Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 9), Array(3, 9)), TrailingMinusNumbers:=True
Selection.NumberFormat = "mm/dd/yy;@"
Columns("G:G").Select
Selection.Style = "Currency"
Columns("K:K").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Columns("O:P").Select
Selection.Delete Shift:=xlToLeft
ActiveCell.FormulaR1C1 = "Currency"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Cntry Code"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Vendor"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Vendor #"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Inv. Date"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Inv. #"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Co. #"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Acct. #"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Dimension"
Range("N1").Select
ActiveCell.FormulaR1C1 = "InterCo. #"
Range("O1").Select
Cells.Select
Cells.EntireColumn.AutoFit
Columns("O:P").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(30, 9)), _
TrailingMinusNumbers:=True
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(30, 9)), _
TrailingMinusNumbers:=True
Columns("D:D").EntireColumn.AutoFit
Columns("I:I").Select
Selection.TextToColumns Destination:=Range("I1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(30, 9)), _
TrailingMinusNumbers:=True
Columns("I:I").EntireColumn.AutoFit
Range("A1").Select
Range("B2").Formula = "=INDEX('[Country Codes recd 022114 (DO NOT DELETE).xlsx] XXXXXCtryLst'!$B$2:$B$5,MATCH(A2,'[Country Codes recd 022114 (DO NOT DELETE).xlsx] XXXXXCtryLst'!$A$2:$A$5,0))"
Range("B2").AutoFill Destination:=Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("B:B").Select
Selection.Copy
Columns("C:C").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Range("O1").Select
ActiveCell.FormulaR1C1 = "IC Code"
Range("O1").Select
Range("O2").Formula = "=IF(OR(N2="""",SUMPRODUCT(--ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9,0},N2)))),""Yes"",""No"")"
Range("O2").AutoFill Destination:=Range("O2:O" & Range("N" & Rows.Count).End(xlUp).Row)
Range("O1").Select
Selection.AutoFilter
ActiveWindow.LargeScroll Down:=1
Range("O27").Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range("O1").Select
ActiveSheet.Range("$A$1:$O$67").AutoFilter Field:=15, Criteria1:="No"
Range("N4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("O1").Select
ActiveSheet.Range("$A$1:$O$67").AutoFilter Field:=15
Columns("O:O").Select
Selection.ClearContents
Range("N1").Select
Columns("N:N").EntireColumn.AutoFit
Columns("N:N").Select
With Selection
.HorizontalAlignment = xlLeft
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("M1").Select
Selection.AutoFilter
Selection.End(xlToLeft).Select
Columns("N:N").EntireColumn.AutoFit
Range("A1").Select
Selection.End(xlToLeft).Select
Range("A1").Select
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
Range("K2:K67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
Range("D2:D67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("A1:N67")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
' PgViewFormat Macro
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
ActiveWindow.View = xlNormalView
Range("A1").Select
Application.ScreenUpdating = True
End Sub