I have a worksheet where I copy data from Table1 to Table2.

Once I have copy-pasted the data to Table2, which sits below Table1, I select the first cell in the first column of Table2 and press `Ctrl`+`Shift`+`Down` to select down to the last used cell. Finally, I apply a concatenation array formula that adds a suffix to the values of the related cells in Table1. I have recorded these Table2 steps as a macro.

To demonstrate, Table1 looks like this:

```
Table1

   |  A      B   C   D   E
---+-----------------------
 1 | Name    V1  V3  V3  V4
---+-----------------------
 2 | Wood    10  10  10  10
 3 | wood    28  28  28  28
 4 | tree    30  45  60  68
 5 | plastic 50  50  50  50
 6 | tree    50  50  50  50
 7 | iron    64  75  75  80

```

This is the formula I use in column A of Table2:

```
{=concatenate(A2:A7," - A")}

```

This is the result after applying it to Table2: 

```
Table2

   |  A               B    C    D    E
---+-----------------------------------
25 | Wood - A        25   25   25   25
26 | wood - A        50   50   50   50
27 | tree - A        50   50   100  100
28 | plastic - A     100  100  100  100
29 | tree - A        100  100  100  100
30 | iron - A        100  100  100  100

```

Now, when I add new entries to Table1, for example in cells `A8` & `A9`: 

```
Table1a

   |  A      B   C   D   E
---+-----------------------
 1 | Name    V1  V3  V3  V4
---+-----------------------
 2 | Wood    10  10  10  10
 3 | wood    28  28  28  28
 4 | tree    30  45  60  68
 5 | plastic 50  50  50  50
 6 | tree    50  50  50  50
 7 | iron    64  75  75  80
 8 | table   20  25  0   30
 9 | plastic 54  35  21  0 

```

after running the recorded macro, instead of using the new range `A2:A9`, it's using the previous recorded range (`A2:A7`), resulting in the `#N/A` errors as can be seen in Table2a:

```
Table2a

   |  A               B    C    D    E
---+-----------------------------------
25 | Wood - A        25   25   25   25
26 | wood - A        50   50   50   50
27 | tree - A        50   50   100  100
28 | plastic - A     100  100  100  100
29 | tree - A        100  100  100  100
30 | iron - A        100  100  100  100
31 | #N/A            #N/A #N/A #N/A #N/A
32 | #N/A            #N/A #N/A #N/A #N/A

```

This is because in the array formula, the range is not dynamic, but fixed.

So, I would like a formula which auto-adjusts the range when new entries are added or deleted from Table1. Something similar to what I have in the recorded macro which selects all the cells in column A of Table2, before applying the array formula:

```
Application.Goto Reference:="R25C1"
Range("A25", Range("A25").End(xlDown)).Select

```

I was thinking of something like the following, where I provide the starting cell of Table1 and the range down to the last cell with data is worked out automatically:

```
Selection.FormulaArray = "=concatenate(Range("A2",Range("A2").End(xldown)).Select,""- A"")"

```

I would like a formula solution, that I can apply to all the cells. I don't want to define variables, etc, to do this.