Auto Update with Vlookup or without Vlookup

[m_vishal_c]

hi, I have huge data in 2 Excel workbooks, i have to fetch data from workbook1 to workbook2. Both files are on shared drive. i tried to use vlookup to fetch data from Worbook1 to Workbook2 but due to having huge data(round about 5000 raws, 50 to 60 columns ), Vlookup does not get refresh automatically, i have to click F2 on particular record and hit enter then it get refreshed data( in workbook2).

Is there any way i can run macro which automatically updates data in workbook2 . Workbook 1 has more then one sheets as well workbook2 too.

Huge thanks in advance

 

[Max1616]

That isn't too much data, Excel should be able to update in real time. Go to the excel ribbon-> Formulas -> Calculation Options (drop down on the right side of the ribbon) and make sure the Automatic box is checked.

Also if you want to force the entire workbook to recalculate you can do this:
excel ribbon-> Formulas -> Calculate Now (right side of the ribbon)

 

[m_vishal_c]

That isn't too much data, Excel should be able to update in real time. Go to the excel ribbon-> Formulas -> Calculation Options (drop down on the right side of the ribbon) and make sure the Automatic box is checked.

Also if you want to force the entire workbook to recalculate you can do this:
excel ribbon-> Formulas -> Calculate Now (right side of the ribbon)

hi thanks for replying. I tried but it does not getting changed. is there any way for auto updation by VBA

 

[Max1616]

Sub Calc()
Dim ws As Worksheet

Application.Calculation = xlManual
For Each ws In ActiveWorkbook.Sheets
    ws.Calculate
Next ws
Application.Calculation = xlAutomatic

End Sub

 

[m_vishal_c]

Sub Calc()
Dim ws As Worksheet

Application.Calculation = xlManual
For Each ws In ActiveWorkbook.Sheets
    ws.Calculate
Next ws
Application.Calculation = xlAutomatic

End Sub

hi Heaps thanks. it works