How to auto populate certain data to new sheet in Excel

If you want to auto populate certain data to new sheet in Excel, you can use the following method. you can download the example file here.

Data table define

Init data

Asssume you have an excel file contains two sheets named “Sheet1” and “Sheet2”
All sheets have the same title row
id, name, price, quantity

we will fetch all quantity > 0 row to a new sheet named “Sheet3”

Define data table

Define data range

select the range of your data in sheet1, then click Data->Get & Transform->From Table

then on the popup window tick the checkbox “My table has headers”, then click “OK”

Edit the query in Query Editor

on the open query editor , right click on “Table1” and click “Advanced Editor”

in the “Advanced Editor” replace the content with following code,then click “Done” and click “Close & Load”

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"name", type text}, {"price", Int64.Type}, {"quantity", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([quantity] > 0))
in
    #"Filtered Rows"

after that excel will automaticly create a new sheet named “Sheet3” and fill the data in it.

Repeat the process for the sheet2 , and delete the new generated “sheet4”

Combine the query result

in the “sheet3” , click Data->New Query->Combine Queries->Append

on the popup window , Second table select “Table3” then click “OK”, then on the query editor , click “Close & Load”

Now excel will auto generate a new sheet “Sheet5” and you may delete “Sheet3”

Add “Refresh” and “Clear” button

in the “sheet5” , add two buttons “Refresh” and “Clear” by Developer->Insert->Command Button , on the popup window need to click “Cancel”


then right click on “Refresh” button, then click “View Code”

in the “View Code” window , replace the content with following code,then click “save” then exit

Sub Button1_Click()
ActiveWorkbook.RefreshAll
End Sub

the same method for the “Clear” button, the code is

Sub Button2_Click()
Sheets("Sheet1").Range("D2:D10").ClearContents
Sheets("Sheet2").Range("D2:D7").ClearContents
ActiveWorkbook.RefreshAll
End Sub

End

Now you have a full function of auto populate and clear function excel, when you click “Refresh” button , all quantity > 0 row will be auto populated to the “Sheet5” and when you click “Clear” button, all data will be cleared. You can download the example file here.