I have 40 sheets with similar headings (or Column titles) and would like to repatriate all the row values into a single sheet without going through the slow process of copying and pasting the rows from each sheet.
thx!
I have the exact same question !! Could someone please help.
I have found a macro on a French website, but for some reason it does not work. I suppose not much change is needed to make it work. You can find it easily with Google as Function Vincent_Copier_Feuilles() but I couldn’t post it here because it is too long.
NB: Feuille means sheet and Fichier means File
I can’t find the opposite of the sheet() function so the formula mentioned won’t work with sheets that are not sequentially numbered :-s
Are you french-speaking? I got the macro working with the following changes:
oDestination = oFeuil.getByName(“Sheet1”) '< This is your target sheet where things will get copied
if left(oUneFeuille.Name,5) = “Sheet” then '< this copies from all sheets where the first 5 characters are “Sheet”.
Needless to say, this will require you to rename your sheets. (but the macro way is still better than my formula suggestion)
As a final option, you can remove the lines with the IF and END IF and it will just copy all sheets.
The code works with the modifications you suggested !!! Thanks a lot !
I deleted the line with “if” and “endif” as you suggested, so it copies automatically all the sheets into the first one, no matter what their name is.
THANK YOU !!!
Challenge accepted! 
This answer is completely academic. Personally, it would solve it with a macro, BUT…
Try this:
=INDIRECT(“Sheet”&TRUNC(ROW()/1001)+2&"!R["&-1000*TRUNC(ROW()/1001)& “]C[0]”,0)
This assumes all your sheets are named sheet2, sheet3, etc. and that you want to consolidate into sheet1
It assumes that you only have 1000 rows per sheet.
You’ll have to copy-paste-values in the end, sort the data and remove all the 0s
Hello,
I have a similar challenge and your formula seems promising.
Please how do I make the Sheet names dynamic so that it takes in any specified sheet name and consolidate
Regards
Thanks for your answer.
Where am I supposed to copy this formula ? In the first cell of sheet 1?
It is better than nothing if it works, but if I have to rename my sheets, the spreadsheet will become useless because I won’t know which one is which.
What about the code I mentionned in my previous comment, any idea ?