automatic pulling data from excel shets to a master sheet

Karbon · 2 Jul 2007 at 15:44

i've been asked to do some stuff in work and it seems to be a lot of manual labour for something that i should be able to automate.... problem being that i have no idea how i would do it.

i have 4 spreadsheets (and one database file but i don't mind doing that manually) that i need to filter, then copy the contents of certain columns into another file. can i do this with macros? or would it be something else?

obvsiouly it's company data so i can't go flaunting the actual data but i'll make up a couple of sheets with some test data if that would help explain the problem better?

Nefarious · 2 Jul 2007 at 16:27

As soon as you have it how you like it in the other file (filtered etc) just select the column (or row) and copy

Paste special and select 'paste link'

Ta da!

Karbon · 2 Jul 2007 at 16:47

i recon i didn't explain this properly... i'm off to draw a picture

that'll do. each bit is a seperate excel file pulled from different locations.

it currenly takes about 30-40 minutes to sort it all and copy all the correct data. i'd idealy like to bring that down.

Nefarious · 2 Jul 2007 at 17:05

Not really sure what your problem is just yet. Does the sorting take so long because it needs automating?

If so that needs to be automated on the work book level and then we can deal with the collation issue.

Karbon · 2 Jul 2007 at 17:10

i need automating because i'm lazy and would like more time in my bed in the morning.

i'll make up some files and stuff with test data in them and upload them somewhere.

Nefarious · 2 Jul 2007 at 17:20

As in, do you not know how to do the filtering and sorting automatically?

How are you doing it at the moment?

Karbon · 2 Jul 2007 at 17:25

i'm currently opening the 4 different files from 4 different locations. filterting them with the filter tool in excel. then copying the contents of the required colums to the master file. in 2 cases this needs to be done column by column because they need to go in a different order.

Nefarious · 2 Jul 2007 at 17:54

Once you've set up the filter parameters why do you keep needing to reset it?

Karbon · 2 Jul 2007 at 18:08

it needs to be done every morning for a different set of data.

i'm probably missing something very simple, or i'm over complicating it for myself.

i'm uploading some test data stuff just now. imagine if you will i only need the data for team 1, everything for team 2 can be ignored. the data for team 1 from each of the 3 (the 4th file can be ignored) needs to be moved from the original to the master file once it has been filtered to ingore team 2.

megaupload is being awfy slow though

*edit* done
http://www.megaupload.com/?d=EZQR16WE

Nefarious · 3 Jul 2007 at 08:54

The files are password protected... care to enlighten us :confused:

Karbon · 3 Jul 2007 at 10:24

whoops, had to password it to email it out from work or the mail server would have taken a fit.

password is dell (go on guess where i work)

Nefarious · 3 Jul 2007 at 11:01

Terribly sorry, I only have M$ 2000 and can't view the files...

From the sounds of things you just need to rearrange the various workbooks to be a little more automated, after that it's just a matter of linking the files.

If you need advice on specific file linking methods and automation instances let me know