I want to use VBA to create a table for import to our ERP system.

Desired state: I press a button on Sheet2, the VBA code iterates through each row of a power query table on Sheet1, then paste each row into Sheet2 and have a row number generated for each iteration. The resulting VBA table should have all the values / rows from the table in Sheet1, as well as row number for each loop iteration and columns with static values for data import formatting purposes (ex: the “min_qty” and “next row” in target table below).

example:

source table in Sheet1:
| Item | Attribute |
| —- | ——— |
| 001 | Red|
| 002| Blue|
| 003|Purple|
|…|…|
|099|Cyan|

Target table from power query in Sheet2 (which will only consist of column headings until the code is run)
| Item | Attribute | row_nbr| min_qty | next row|
| —- | ——— | ——- | —— | ——– |
| 001 | Red|1|1.01|yes|
| 002| Blue|2|1.01|yes|
| 003|Purple|3|1.01|yes|
|…|…|…|…|…|
|099|Cyan|99|1.01|yes|

The length of the item dimension table in Shee1 is variable, so I would need to have the iterator stop at the end of the table object or when the value in that table is a blank string (i.e. “”), whatever is best practice.

One last thing, to avoid replicating data imported to the ERP system, I would want to have the VBA code clear out all of the cell contents of the VBA table’s destination range if any exists. I am thinking something like what the CurrentRegion function does to select all tabular data. For example, if last import was 700 rows, but this one is 600 rows, there will be 100 rows from the last import if VBA doesn’t clear out old contents.

I really appreciate any guidance, as I have no experience with VBA coding myself.