If you want to replace data from a closed workbook ,you can do this process fairly quickly with ADO .
Before, you can choose the workbook that you want to modify. Then, any sheet of listed sheets in the drop-down box is selected .
Data in “Column A” was used in this macro.
When the button is pressed,data in “Column A” are listed into “Column G” and “Column H” with the number of repetitions.
It’s video :