Excel VBA to Build/Recreate a table from a Name Range with Data

Two parts to the project, one using VBA, other using Excel Functions

I need VBA function to remove the existing data range (a fake table), then recreate it based on the data on sheet “STOCK”, rebuilding it with only lines that have data then using sumif/countif to populate the table with totals from the “STOCK” sheet, so there is no duplicate or lines without stock.

Spreadsheet has 3 sheets :-
1. List of data in name ranges (fruits and warehouses) on a sheet “_name data”
2. Current stock in a table on a sheet “STOCK”
3. Tidy report without any fruits/warehouses with no stock or duplicates on a sheet “REPORT”

The tables it is building are not actual Excel tables, just a range of cells with headers.

On the final application where this is used, there will be other rows / columns adjacent to these report tables so its important that the code understands its boundaries (i.e. uses the range of the table) and doesn’t delete/overwrite any other data on the sheet.


PART 1
There is a button next to the table on the report sheet, which once clicked, needs to remove all the data in that table, then rebuild it based on data on the report page, leaving out any lines which have no stock and NOT have any duplicates. It does not need any of the figures to be copied.

So for example, if there is 3 lines of Apples in Aylesbury, it should only have 1 line in this table. But if theres also Apples in another warehouse i.e. London, then it should also have this line.

PART 2
Now the table has been built with no stock, it needs to tally up the totals for the various fruit + warehouses from the stock sheet using excel functions such as sumif/countif (no vba).


Ive added the sample spreadsheet to the project so you can see what its all about.

Any questions please ask, I need it to work as described for a fixed small budget. I’d normally do it myself but am suffering with migraines and just need it done for a friend.

Nice simple job