Sheets("csv1").Activate
wb2 = Application.GetOpenFilename _
(Title:="Please choose CSV to import")
If wb2 = False Then
MsgBox "No file opened", vbInformation, "No file opened"
Exit Sub
Else
Workbooks.Open Filename:=wb2
End If
Application.ScreenUpdating = False
Set wbtwo = ActiveWorkbook
Range("DM:DN,DP:DQ,DS:DT,DV:DW,A:C,E:Y,AA:AB,AD:AE,AG:AH,AJ:AK,AM:AN,AP:AQ,AS:AT,AV:AW,AY:AZ,BB:BC,BE:BF,BH:BI,BK:BL,BN:BO,BQ:BR,BT:BU,BW:BX,BZ:CA,CC:CD,CF:CG,CI:CJ,CL:CM,CO:CP,CR:CS,CU:CV,CX:CY,DA:DB,DD:DE,DG:DH,DJ:DK").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Selection.End(xlDown).Select
CSVEndRow = Split(ActiveCell(1).Address(1, 0), "$")(1)
Range("A1").Select
Selection.End(xlToRight).Select
CSVEndCol = Split(ActiveCell(1).Address(1, 0), "$")(0)
Range("A1:" & CSVEndCol & CSVEndRow).Select
Selection.Copy
Workbooks("SS").Sheets("CSV1").Activate
Range("A1").Select
Selection.PasteSpecial xlPasteValues
Application.DisplayAlerts = False
wbtwo.Close
Application.DisplayAlerts = True
Range("A1").Select
For Each x In Range("A1:A" & CSVEndRow)
x.Value = WorksheetFunction.Proper(x.Value)
Next x
With Application.SpellingOptions
.SuggestMainOnly = True
.IgnoreCaps = True
End With
With Range("A1:A" & CSVEndRow)
Call .CheckSpelling
End With
'Exit Sub
Dim ccount As Integer
ccount = 0
'-----------------------------------------------------------------------
For Each x In Range("A2:A" & CSVEndRow)
x.Value = Trim(x.Value)
If x.Value = "Line Manager" Then
GoTo Skipx
Else
If x.Value = "Manager" Then
x.Value = "Line Manager"
GoTo Skipx
Else
If x.Value = "Direct Report" Then
GoTo Skipx
Else
If x.Value = "Self Assessment" Then
GoTo Skipx
Else
If x.Value = "Self" Then
x.Value = "Self Assessment"
GoTo Skipx
Else
If x.Value = "Colleague" Then
GoTo Skipx
Else
If x.Value = x & " " Then
GoTo Skipx
Else
x.Interior.ColorIndex = 8
ccount = ccount + 1
End If
End If
End If
End If
End If
End If
End If
Skipx:
Next x
If ccount >= 1 Then
Application.ScreenUpdating = True
MsgBox (ccount & " errors found." & vbNewLine & "Please amend before continuing")
Sheets("csv1").Activate
Range("A1").Value = "Press CTRL + SHIFT + S to resume"
MsgBox ("Please press CTRL + SHIFT + S to resume")
Exit Sub
Else
Call SS2
End If
It doesn't seem to call the second Macro, but doing CTRL + F8 through the code makes it work fine.
Bookmarks