Raymundo
02-27-2020, 10:40 AM
I need a macro to copy data from a query table to a pre-defined sheet every time the query is refreshed (automatically) and continuously “push” the data through a specified number of sheets within the workbook. This task is easy when manual, but when trying to make it automatic, it seems to be quite complex. I’ve tried things like worksheet change, worksheet calculate, afterRefresh event, etc.. For whatever reason Microsoft/Excel has made this task difficult to automate.
Example of Operations:
A query table (let’s say in Sheet1 (RawData) of workbook (Numbers.xlsm) is refreshed from a web data source (using Power Query) at a user-specified time (defined in the Power Query property options). For the sake of example, let’s just use 1 minute as the refresh interval.
After the query table has been refreshed, every minute, a copy of that most recently updated table/data (from Sheet1 (RawData)) should be copied and pasted into Sheet2 (1minute).
Data from Sheet2 (1minute) should then be copied and pasted into Sheet3 (2minutes) and then Step 2 is fired again. This should continue to push data through the different sheets until it reaches Sheet6 (5minutes). After that it can stop pushing data to different sheets but continue to automatically refresh and update the data in the various sheets to create a rolling data stream of the 1,2,3,4,5 minute data/tables.
Any help in setting this up would be greatly appreciated!
Example of Operations:
A query table (let’s say in Sheet1 (RawData) of workbook (Numbers.xlsm) is refreshed from a web data source (using Power Query) at a user-specified time (defined in the Power Query property options). For the sake of example, let’s just use 1 minute as the refresh interval.
After the query table has been refreshed, every minute, a copy of that most recently updated table/data (from Sheet1 (RawData)) should be copied and pasted into Sheet2 (1minute).
Data from Sheet2 (1minute) should then be copied and pasted into Sheet3 (2minutes) and then Step 2 is fired again. This should continue to push data through the different sheets until it reaches Sheet6 (5minutes). After that it can stop pushing data to different sheets but continue to automatically refresh and update the data in the various sheets to create a rolling data stream of the 1,2,3,4,5 minute data/tables.
Any help in setting this up would be greatly appreciated!