Error on .Range ???

' Open the TOAD Excel Spreadsheet
Dim strPsoftExtract
strPsoftExtract = "Y:\HT_BI_HEE_EXTRACT_V2.xls"
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(strPsoftExtract)
Dim frmla
Dim i 
Dim rg 
objExcel.ScreenUpdating = False

'Insert column headers in col Z
objExcel.Cells(1, 25).Value = "ORG UNIT1 NAME"

With objExcel.ActiveSheet
	Set rg = .Range("Y2") 
	Set rg = .Range(rg, .Cells(.Rows.Count, rg.Column).End(-4162)) 'xLUp = -4162

'This inserts the VLOOKUP formula into cell Z2, AA2.
	For i = 1 To 2
    	frmla = "=VLOOKUP($Y2,'J:\[ManagerLevels.xls]sheet1!$A$2:$D$16," & (2 * i) & ",FALSE)"
    	.Range("Z2").Offset(0, i - 1).Formula = frmla
	Next
End With

'This copies the formula all the way down columns Z and AA.
'and then replaces the formula result with the value only (like doing Paste Special --> Values)
With rg.Offset(0, 25).Resize(, 2)
    .FillDown
    .Formula = .Value
End With

objExcel.ScreenUpdating = True

' Retrieve the separate Date components. 
Dim TodayYYYY, TodayMM, TodayDD
TodayYYYY = Year(Date) 
TodayMM   = Month(Date) 
TodayDD   = Day(Date) 

' Save the sheet - appending TodaysDate to the end of the file-name.
objWorkbook.SaveAs "Y:\HT_BI_HEE_EXTRACT_V2_" & TodayYYYY & "-" & TodayMM & "-" & TodayDD & ".xls"

' Delete the original Psoft Extract
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.DeleteFile strPsoftExtract, True

' Close Excel with the Quit method on the Application object.
objWorkbook.Application.Quit

' Release the object variable.
Set objExcel = Nothing

Select allOpen in new window