Ask Your Question

How can I merge separate sheets with similar headings into a single one? [closed]

asked 2014-02-12 15:12:57 +0200

fonster90 gravatar image

updated 2014-05-20 18:34:04 +0200

lonoob gravatar image

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!

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-23 04:15:07.901256



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

lemar gravatar imagelemar ( 2014-05-20 18:24:54 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2014-05-20 22:40:55 +0200

CEAuke gravatar image

updated 2014-05-20 22:56:19 +0200

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.

edit flag offensive delete link more



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 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

lemar gravatar imagelemar ( 2014-05-21 15:17:50 +0200 )edit

answered 2014-05-20 21:07:18 +0200

CEAuke gravatar image

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

edit flag offensive delete link more



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


bunmalik gravatar imagebunmalik ( 2019-03-03 07:48:44 +0200 )edit

answered 2014-05-20 22:21:20 +0200

lemar gravatar image

updated 2014-05-21 15:21:43 +0200

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 ?

edit flag offensive delete link more

Question Tools



Asked: 2014-02-12 15:12:57 +0200

Seen: 1,234 times

Last updated: May 21 '14