Can Excel Power Query Create a New Worksheet on Every Refresh?
Question details
The user wants to preserve each Power Query refresh as a separate worksheet, including rows that may later be deleted from the source.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A temporary table changes repeatedly, and the user wants a separate snapshot of its contents for each refresh without writing code.
- Observed behavior
- Power Query normally refreshes its existing output rather than creating a new worksheet for each run.
Make sure the query is loaded to a worksheet table so you can copy its results. Refresh and save a snapshot while the rows you want to preserve are still present in the source.
Save each refresh manually as a values-only worksheet
Use this no-code method to preserve the current query output as a separate snapshot.
In Excel, select Data > Refresh All and wait for the query output table to finish updating.
Select the query output table, including its headers, and press Ctrl+C or use the Copy command.
Select the plus button beside the worksheet tabs to add a new sheet. Select cell A1, then use Home > Paste > Values to paste a static copy.
Rename the new worksheet with the refresh date or another identifier, then save the workbook. Repeat these steps after each refresh you want to keep.

Automate refresh snapshots with a macro or script
Use automation if a new worksheet must be created after every refresh without manually copying the results.
Review Excel snapshot workbooks in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface for working with spreadsheet files. Open compatible Excel workbooks and review saved snapshots in WPS Spreadsheets; it does not automate Microsoft Excel Power Query refreshes.

Frequently Asked Questions
Does Power Query keep a history of deleted source rows?
Not by default. A refresh reflects the data returned by the query at that time. Copy the output to a separate sheet before the rows disappear if you need a snapshot.
Can I create a new sheet for each refresh without code?
You can do it manually: select Data > Refresh All, copy the output table, add a worksheet, and use Home > Paste > Values. Repeat for each snapshot.
Will copying the query output as values update the snapshot later?
No. Paste > Values creates a static copy, so later query refreshes do not change that snapshot.
Can VBA create a worksheet after a Power Query refresh?
Yes. A VBA macro can be programmed to refresh the query, wait until it finishes, add a worksheet, and copy the results. Open Developer > Visual Basic to create or edit the macro.




