Merging worksheet data

Hi there - another new user here.

This has been bugging me all day so any help or suggestions would be really gratefully appreciated.

I have 27 worksheets each containing purchasing data from different businesses. There are 6 columns in each sheet showing a purchase route, each row represents a supplier and each cell represents a value (so A = "Direct", 1="AAA Ltd" and A1 = £200)

Although the Supplier names are all similar they are not all the same (and there is a variance on the number for each sheet (some have 300, some have up to 400)) meaning that vlookup is not really working.

What I want to see is the spend of each company by supplier, also showing the purchase routes so I can compare each company's total spend by purchase route/supplier or by supplier total.

I have tried using pivot tables, but with no success (as the data is across worksheets) and the less said about my access attempt the better.

So, how do I get all the data to show neatly (and visibly) on one sheet and still be able to manipulate it to extract the information I need?

Many thanks

L

The first thing I would do is get all that data into one worksheet.

Ron de Bruin has some code that might make it easier:
http://www.rondebruin.nl/copy2.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Then maybe the pivottable would look nicer.

leecou wrote:
>
> Hi there - another new user here.
>
> This has been bugging me all day so any help or suggestions would be
> really gratefully appreciated.
>
> I have 27 worksheets each containing purchasing data from different
> businesses. There are 6 columns in each sheet showing a purchase route,
> each row represents a supplier and each cell represents a value (so A =
> "Direct", 1="AAA Ltd" and A1 = £200)
>
> Although the Supplier names are all similar they are not all the same
> (and there is a variance on the number for each sheet (some have 300,
> some have up to 400)) meaning that vlookup is not really working.
>
> What I want to see is the spend of each company by supplier, also
> showing the purchase routes so I can compare each company's total spend
> by purchase route/supplier or by supplier total.
>
> I have tried using pivot tables, but with no success (as the data is
> across worksheets) and the less said about my access attempt the
> better.
>
> So, how do I get all the data to show neatly (and visibly) on one sheet
> and still be able to manipulate it to extract the information I need?
>
> Many thanks
>
> L
>
> --
> leecou
> ------------------------------------------------------------------------
> leecou's Profile: http://www.excelforum.com/member.php...o&userid=27791
> View this thread: http://www.excelforum.com/showthread...hreadid=473118

--

Dave Peterson

Thanks Dave,

This is my first go with macro's and it looks a bit daunting. I'll post back in 24hrs and let you know how i get on.

L