Merging XL files

[sargan]

I have a weekly task.
A large XL file is issued, it has a lot of formula's, multiple tabs .. and a large pivot table on the summary tab .... taking feeds from multiple Tabs.

I filter it for my country .. and issue it to my team ... they update the file ... I then have to merge the 8-10 files back into one single file for my submission.

They filter on their name and update their entries .... (currently 750+ rows on their input Tab)
As the team may be deleting rows or adding rows, therefore row numbers are always changing, plus they are non contiguous.
They highlight additions or particular items with colour. (we can then use filter by colour on some reporting)

The steps used to merge are very manual, convoluted & error prone ... plus any colour highlights get removed.

# Open master file and filter by 'John'
# Open input file from "John"
# highlight rows .... then use 'select visible cells' (as not contiguous row numbers) & copy
# On master file, on next empty line paste in the entries from "John' using paste formulas
# highlight the original rows ... .... then use 'select visible cells' & use 'right-click' delete line to remove them.

All formula's on top row need updating to reflect new line count range ...

Then repeat for each of the other 8 or 9 files to be merged in.

On summary page I cannot just use 'refresh' on the pivot table as data range is wrong.
I have to go to "Pivot Table Tools - Analyse' and then select 'change data source' the manually update data range to reflect the new last line number.


This as mentioned loses any colouring of cells ....

Is there a better way of merging sheets into common master book, are there any automation tools ?

[FDibbins]

You could probably record a macro do do that all for you, provided the file names, columns etc, all remain the same each update?

1. make sure you SAVE your file - and maybe even make a copy so you wont lose anything.
2. practice what you want to do a few times, so you know exactly what you are doing.
3. click the macro record button - bottom left, next to the READY icon.
4. Give the macro a name that means something relative to what you are doing.
5. go through the steps as you would normally
6. when finished, click the record button again

your macro is now ready. you can activate it either through a button (you will need to create/add this and allocate the macro to it) or you can select VIEW on the ribbon, MACRO/view and click on the macro.

[Richard Buttrey]

Hi,

Why not first delete all John's rows from the Master file before opening his file and copying back all his records.

All formulas and the Pivot Table Source range should use a dynamic range name for the data that automatically adjusts to cover the data range.

I'm also assuming you have automated this with a macro, if not that should be done too.

And a heresy suggestion since most of us advise against using the Shared Workbook functionality for various reasons, but maybe, just maybe this might be one of the rare occasions when the advantages of a Shared Workbook may outweigh any disadvantages.

[sargan]

You could probably record a macro do do that all for you, provided the file names, columns etc, all remain the same each update?

.

The columns remain the same ..
How would the record function work for the manual entry of line numbers into the Column header formulas and the pivot table data range.
The 'row value will change each time.

[FDibbins]

I was thinking that you could batch-copy each person's data, to where you needed it to go. If you have blank rows at the end of the process, those would be relatively simple to remove.
You may need to recreate the PT.

However, my thinking is that if you at least try and keep things to a standard size, format, location, etc, this will simplify the process for you.