Macro Stopped Working to Ask for File Location

ScoutGMa2

New Member
Joined
Oct 9, 2017
Messages
1
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top