That’s the VBA equivalent of pressing Data | Refresh All. ![]() What we want to do is refresh the query which is simply this line: ActiveWorkbook.RefreshAll If Not Application.Intersect(CellsChanged, Range(Target.Address)) Is Nothing ThenĮnd If ‘ change in one of the CellsChanged range ‘ change to whatever range applies to the source table ' CellsChanged is the cells that will trigger an action if changed. ![]() The basic function looks like this: Private Sub Worksheet_Change(ByVal Target As Range) For that we turn to VBA and one of its event handlers.Įxcel VBA has an automatic trigger or event when there’s a change in a worksheet: Worksheet_Change(ByVal Target As Range) in that function you can add code to only work when certain cells in the worksheet are changed. The better fix is to force a query update whenever a cell in the source table is changed. None of these are good options because there will always be a time delay between changing the original table and the transposed table updated. ![]() Refresh this connection on Refresh All should be ON. Refresh data when opening the file should be ON. Refresh every … minutes might be enough but it still means the two tables could be out of sync.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |