Copy sheets from another workbook with Bloomberg formulas

Question

I wrote a simple VBA to copy paste sheet content from another workbook. However, that sheet contains Bloomberg formulas which need to be refreshed before copying. How do I refresh the Bloomberg formulas on the other workbook before copying it to my current workbook, without opening it?

Code is below:

Sub foo()

Dim wb As Workbook
Dim sht1 As Worksheet
Dim current_sht1 As Worksheet
Dim FilePath As String
Dim shtName1 As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

FilePath = Range("Path") & "FILENAME"
shtName1 = "SHEETNAME"

Set current_sht1 = ThisWorkbook.Worksheets(shtName1)
current_sht1.Cells.Clear

Set wb = Application.Workbooks.Open(FilePath)
Set sht1 = wb.Worksheets(shtName1)

sht1.UsedRange.Copy
current_sht1.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
current_sht1.Range("A1").PasteSpecial xlPasteFormats

Application.CutCopyMode = False
wb.Close SaveChanges:=False
Application.ScreenUpdating = True

End Sub

Also the file I am working with contains a ton of Bloomberg real time formulas, which is hard to work with during market hour. Is there any way to make the data feed more efficient? And I don't want to uncheck the real time option as we kinda want it to be real time...

Thank you!

Answer 1

By your code I assume sht1 is the sheet with the Bloomberg formulas on it. Do a recalulate of the sheet befory copying like that

sht1.Calculate