Good afternoon,
I have created the code below with thanks to people on this group, but I am looking at slimming the data down in the current file so I am hoping to look at instead of a formula being entered in column "K" as per the below.
As I have the data for the vlookup already in my destination file is there anyway I can just have the value showing instead of the fornula
Thanks in advance
I have created the code below with thanks to people on this group, but I am looking at slimming the data down in the current file so I am hoping to look at instead of a formula being entered in column "K" as per the below.
As I have the data for the vlookup already in my destination file is there anyway I can just have the value showing instead of the fornula
Code:
Dim x As Workbook, y As Workbook
Dim r As Range
Dim ts As Date
Application.ScreenUpdating = False
Sheet4.Visible = True 'Adherence
Sheets("Adherence").Range("A2:K5000").ClearContents
If Not bFileOpen("Adherence and Conformance Report.xlsx") Then Workbooks.Open ("Adherence and Conformance Report.xlsx")
If Not bFileOpen("Stats for Agents.xlsm") Then Workbooks.Open ("Adherence and Conformance Report.xlsx")
Set x = Workbooks("Adherence and Conformance Report.xlsx")
Set y = Workbooks("Stats for Agents.xlsm")
y.Activate
x.Sheets("RawData").[A2:J3000].Copy [A2]
Set r = [E2:H3000]
r = Evaluate("IF(" & r.Address & "="""",""""," & r.Address & "/86400)")
r.NumberFormat = "hh:mm:ss"
x.Close
' formula to a value based here '
With Range("K2:K" & Cells(Rows.Count, "A").End(3).Row)
Application.DisplayAlerts = False
.Formula = "=IF(A2="""","""",IFERROR(VLOOKUP(B2,source!B:N,13,0),""Leaver""))"
.Value = .Value
End With
Sheets("Welcome").Select
Range("E13").Select
Range("E13").Value = Format(Now(), "mm/dd/yyyy hh:mm")
Sheet4.Visible = xlVeryHidden
End Sub
Function bFileOpen(wbname As String) As Boolean
On Error Resume Next
bFileOpen = Len(Workbooks(wbname).Name)
On Error GoTo 0
End Function
Thanks in advance