formula giving wrong values after copying another sheet

[narsing rao]

i have small problem with workbook "data_base" ...after copying the data to another sheet the formula in column "D" should show

like this below

"=IF([@[Time Out]]="","",([Time Out]-[Time In])*24)"

but its showing as below

"=IF(TTM_Form.xlsm!Table2[@[Time Out]]="","",(TTM_Form.xlsm!Table2[Time Out]-TTM_Form.xlsm!Table2[Time In])*24)"

and i am not getting the proper results ...can you please let me know how to avoid it. code line below
what ever the comments i have tried all methods but non worked.

Option Explicit

    Sub DataBasedOnDate1()
    Dim wbSource As Workbook, wbDestination As Workbook
    Dim startDate As Date, Enddate As Date, dtTodayDate As String
    Dim Source As Worksheet, Destination As Worksheet
Dim lRow As Long, nRow As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
startDate = Sheets("workform").Range("D6").Value
Enddate = Sheets("workform").Range("D7").Value
dtTodayDate = Format(Date, "mmm-dd-yyyy")
Set wbDestination = Workbooks("Data_Base.xlsm")
Set Destination = wbDestination.Sheets("Logintime")
Set wbSource = Workbooks.Open("D:\TMS_Project\TTM_Form.xlsm")
Set Source = wbSource.Sheets("Data Sheet")
With Destination
    .UsedRange.Clear
End With
With Source
    .AutoFilterMode = False
    lRow = .Cells(Rows.Count, "C").End(xlUp).Row
    With .Range("B2:F" & lRow)
        .AutoFilter Field:=5, Criteria1:=">=" & CDbl(startDate), Operator:=xlAnd, Criteria2:="<=" & CDbl(Enddate)
        'Destination.Range("A1").Formula = Source.Range("F1").Formula
        .SpecialCells(xlCellTypeVisible).Copy Destination:=Destination.Range("A1")
         'wbDestination.Sheets("Logintime").Range("D2").Value = .Range("D2").End(xlDown).Offset(0, 41).Value
        '.SpecialCells(xlCellTypeVisible).Copy
        'Destination.Range("A1").PasteSpecial xlPasteFormulas
         wbDestination.Sheets("Logintime").Columns(5).EntireColumn.Delete
         wbDestination.Sheets("Logintime").Range("A:D").Rows.AutoFit
             Dim Lastrow As Long
             Lastrow = Range("A" & Rows.Count).End(xlUp).Row
            'wbDestination.Sheets("Logintime").Range("D2:D").Formula = "=TEXT(C2-B2,""hh:mm"")"
         wbDestination.Sheets("Logintime").Activate
  
         wbDestination.Save
         wbSource.Close
    End With
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

​

 

[Chihiro]

Upload sample workbook that replicates your issue.

Without it, I can only suggest that you put formula into string variable and put it into destination range.

 

[NARAYANK991]

Hi ,

Uploading a sample workbook with adequate data and the code in it will help , but in case you cannot , first enter the formula in the worksheet and see whether the result is correct.

The structured reference [@[Time Out]] refers to the value of the Time Out field in each row , as a single element ; the @ symbol is used to refer to the single element on each row.

The structured reference [Time Out] refers to the entire column of data in the field labelled Time Out. Similarly , [Time In] refers to the entire column of data in the field labelled Time In.

Thus , the second part of the IF statement will result in an array of values ; is this what you want ? Unless this is entered over the entire column as an array formula. I don't see any usage of FormulaArray anywhere in your code.

Narayan

 

[narsing rao]

in this i am copying the data from TTM_Form to data_base form , in data_base work book i have sheet called workform in that if you click login hours
button data should copy from TTM_Form to data_base "logintime" sheet.

if you need more clarification let me know

 

[Chihiro]

Why copy formula? You already have calculation done on TTM_Form.

Also, another issue when copying and pasting filtered table, is that destination will not retain Excel Table format.

Just change code to something like below.

        .SpecialCells(xlCellTypeVisible).Copy
        Destination.Range("A1").PasteSpecial xlPasteValues
        Destination.Range("A1").PasteSpecial xlPasteFormats

 

[narsing rao]

the changes you have mentioned seems working...but still i need to check for more time as my project in development stage ...i will defiantly keep you posted on this ..thanks for the help...