Data Merging in R for 12 excel workbooks (each containing 3 worksheets)
may my answer can help you:

Excel has limit 1.048.576 rows
You just need package to import excel file (readxl...). You can use for loop to import all file, and merge all file to dataframe and export to excel.
In VBA, i think logic is same in R
I asked our data support team to share data for 12 months, they sent me 12 different files with 3 sheets in each file. I need to combine all of that data into another datasheet, I have the following questions?

Would excel be able to cram in all the data into one large file, limitations?
Is R a good solution, can one share any easy code and libraries needed for such operation, seen multiple videos on youtube but all are not working.
I heard that in Python Pandas is helpful but my past experience is bad that Python being very slow.
I have no idea of VB codes

Please could anyone help.