Im getting the Error 1004 Method Range of Object Worksheet failed, not sure what Im missing here...
VBA Code:
Option Explicit
Sub CleanUp()
Dim wbImport As Workbook
Dim wsImport As Worksheet
Dim cell As Range
Dim fileNameAndPath As Variant
Dim lDestLastRow As Long
'Open a workbook
fileNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.csv), *.csv", Title:="Select File To Be Opened")
If fileNameAndPath = False Then Exit Sub
'Workbooks.Open Filename:=fileNameAndPath
Set wbImport = Workbooks.Open(Filename:=fileNameAndPath, ReadOnly:=True)
Set wsImport = wbImport.Worksheets(1)
Application.EnableEvents = False
'Add Zero in front of the mobile number
With wsImport
.Range("Q3:Q" & lDestLastRow).NumberFormat = "@" 'format range as text
For Each cell In .Range("Q3:Q" & lDestLastRow)
cell.Value = Format(cell * 1, "0000000000") 'Convert each cell
Next cell
End With
'Remove spaces from the mobile number
With wsImport
.Range("Q3:Q" & lDestLastRow).Replace " ", vbNullString, xlPart
End With
'Clear contents of existing data range
wsImport.Range("AO3:AY,BB3:BF " & lDestLastRow).ClearContents
'Close the Source Workbook
wbImport.Close SaveChanges:=False
MsgBox "Added Zeros in front of the Mobile Number and Deleted Employer Info not required", vbOKOnly
Application.EnableEvents = True
End Sub
