Quote:
first i triend a for next loop to return single cell value inside the resulting horizontal array , but i failed
The reason you failed is in this line:
ActiveCell.Insert shift:=xlRight
As
Microsoft documentation[
^] says:
ActiveCell
returns a
Range
object that represents the active cell in the active window (the window on top) or in the specified window.
So,
ActiveCell
refers to the same cell inside a loop.
You should refer to the cells in a
Source
range, then copy them whenever you want:
Dim c As Range
For Each c in Source.Cells
c.Copy ...
Next
More:
Range.Copy method (Excel) | Microsoft Learn[
^]