Page 1 of 1

Merge sheets of one spreadsheet document

Posted: Thu Apr 05, 2018 4:23 pm
by Villeroy
2019-01-14: Found a minor glitch in the original version which made it possible to merge the target sheet with itself when a multiple cell range was selected on target only.
The macro ignores hidden sheets now.
The macro releases the multi-sheet selection when it is done.
I wrapped the code into an example document attached below.


This macro is derived from Merge sheets of spreadsheet documents. That macro is embedded in a spreadsheet template. It reads spreadsheet files from a folder and merges the used areas of a specific sheet below a specified target cell within the document embedding the macro code. You create a new document from that template, store it with all the settings and reuse the same file whenever new files arrived.

Here is another variant of the same macro reading the used areas of selected sheets and writing the raw data below a specified target cell. Because it is supposed to work with any spreadsheet document (not just the embedding document), you may save the code somewhere in your global container "My Macros".

The macro copies data from the used areas of all selected sheets ignoring any hidden sheets and the target sheet. The code skips the specified amount of header rows and dumps the raw data (strings and values, no formulas nor formattings) below the current region of the target cell.
The data will be dumped with one column offset because the first column below the target cell gets the names of the source sheets so you won't lose any information.
Since this macro inserts the required amount of new rows below the target cell, all formula references, names, charts, pivot tables etc. that refer to the target area will adjust to the new size of the expanding target area. Any data and formulas below the target area won't be overwritten by the expanding range.
Since the macro inserts new rows below the current region, any formatting from the last existing row expands to the inserted rows. This way you always get a uniform formatting since the macro copies only the raw data from the source sheets without formulas and disregarding any formatting.

Usage
1) Import the library "MergeSheets" from this document into your library tree below "My Macros" or copy the 2 modules to an existing library.
2) Make sure that all source sheets in your document have the same structure. Same amount of columns in same order of columns, no column headers or same amount of column headers above the data rows. The position and size of the data range does not matter the macro copies the used areas of the selected sheets skipping any specified amount of header rows. Refer to my example document, sheets "Feb", "March", "May", "June".
3) On an extra sheet name a cell "Target" which marks the top-left corner of the import area. Any existing data below "Target" will be preserved. The macro uses the current region below the target cell and inserts new rows as needed. The "current region" around a cell is the range of adjacent non-blanks. You may put formulas, charts and other stuff below the target area at a distance of at least one empty row. See example document.
How to name a cell: click the wanted cell and type the name into the name box left of the formula bar.
4) If your source sheets have one or more header rows above the data to be merged, define another named cell "Headers" where you specify the amount of rows to be skipped. Refer to the "Target" sheet in my example document.
5) Select the source sheets or select all sheets after hiding any sheets you don't want to be copied. Your selection may include the target sheet (the one with the "Target" cell) which will be ignored anyway.
6) Call the Main routine of Module1 from the library you imported in step 1).

If you start with an empty area below the target cell, format a first dummy row below the target cell and remove the dummy row when the job is done.
Or just run the macro and format afterwards. You know how easy it is to work with cell styles, don't you?