Hello All -
Background
- FactSet is a financial software, similar to Bloomberg. FactSet, again similar to Bloomberg, has a non-native ribbon when the excel addon is installed.
- The excel shortcut to "Refresh all FactSet formulas" in the workbook, is ALT+S+R+W.
Macros Purpose
- When macro is submitted, insert new columns on different tabs.
- Simulate keyboard submitting of ALT+S+R+W (to update all FactSet formulas in workbook).
- Macro to wait 20 seconds, in order for FactSet formulas to actually update.
- Then, proceed with copy/paste information to different tabs etc.
Macro Issue/Problem
- At least with how VBA is written now, the application.wait function is being triggered the moment the macro is run, even though in the code, this should occur after application.sendkeys to simulate ALT+S+R+W.
Thread Purpose
Has any user seen application.wait go "out of turn" sort of speak? I cannot figure out how to get the macro to pause between simulating ALT+S+R+W, and copy/pasting data to different tabs. Any thoughts?
VBA (also attached in word doc)
Code
Sub factsetrefresh()
' Insert new column in Archer Archive sheet
Sheets("Archer Archive").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
' Insert new column in Tyler Archive sheet
Sheets("Tyler Archive").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
' Simulate pressing Alt+SRW keys
Application.SendKeys "%", True
Application.SendKeys "S", True
Application.SendKeys "R", True
Application.SendKeys "W", True
' Wait for 20 seconds
Application.Wait Now + TimeValue("0:00:20")
' Copy values from Formula sheet column B and paste in Archer Archive sheet column B
Range("B8").Select
Sheets("Formula").Select
Columns("B:B").Select
Selection.Copy
Sheets("Archer Archive").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
' Copy values from Formula sheet column F and paste in Tyler Archive sheet column B
Sheets("Formula").Select
Columns("F:F").Select
Selection.Copy
Sheets("Tyler Archive").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
' Save active workbook
ActiveWorkbook.Save
End SubModerator Comment: I have added Code Tags to your VBA. Please read the forum rules you agreed to abide by when you joined. These include how to add code tags.