Combining sheets with variable layout

[glyn]

Hi I imagine this must of come up before and there must be a solution
that already exists somewhere.

I regularly receive multiple workbooks or workbooks with multiple
sheets that need to be combined to one file to produce a list of
addresses etc.

curently I manually sort them to a standard layout and have VB scripts
of my own that will then combine either the books or the sheets
(mainly books).

I would love to have a soloution that will automatically combine these
sheets into one file removing the human error factor of getting one or
more out of sequence.

Points to note:

The data layout can change and may or may not include the same number
of fields/columns

I would rename the header in each book/sheet to be consistant (only
way i can see something working)

so in summary what i need is something that will arrange the books/
sheets into the same order and i can then combine them with the code I
already have.

Or something that will arrange and combine them.

I could simply do this with access by importing each file into a
standard table but it runs the risk of missing or duplicating a file.

Any info on a solution that exists or where to start programming one
would be of great help

 

[Ron de Bruin]

Hi

I have a lot of examples on my site
See if something is working for you

Maybe you can use the merge add-in ?
See the "Copy/Paste/Merge examples" section

 

[glyn]

Thanks, looks interesting I will have a play with a few.

 

[glyn]

Hi Ron,

Great site

Had a quick play with the merge addin, fantastic addin however it does
not account for the fact that the data layout can be different in the
files I have eg

file 1
column A - Name
column B - Address
column C - Telephone

File 2
column A - Address
column B - Name
column C - Telephone

I need to shuffle all the data to be in the same layout this is the
crux of whats stumping me at the moment.

Glyn

 

[glyn]

No one any ideas on the simplest way to shuffle data on different
sheets or books to the same layout?