Extract Data from worksheet & copy to workbooks

[floricita]

Hi there.

I was wondering if someone can help me with the following problem please.

Basically I have data sheets, which has things like Area, Region, spend etc. Each line has a unique identifier.

I then have a seperate workbook for each Area/Region.

What I would like to do, is exttract the relevent data for that Region/Area from the data sheet and put it into the Area/Region workbook, without haviong to cut and paste a million times, because there is a lot of data.

Is this possible at all?

Thank you.

[JBeaucaire]

All of this is possible and probably done frequently. I have several macros on my website that might assist.

I would recommend parsing the data from your master worksheet to separate SHEETS in the same workbook, much easier to manage. Here's a macro for that:

Sheet1 to Many Sheets

I would work with the main sheets in one workbook for expediency. You can copy sheets to individual workbooks later, for dispersal, here's a macro that does that:

Sheets to Workbooks

[floricita]

Thanks a lot for that I will try that very soon. That is where I wanted to end up.

In some cases I am already at that stage without using macros but using v-look ups.

And I wondered how you would get the tab from that spreadsheet which has come from the master data tab, into a new workbook entirely?

Ideally also I'd like a macro that send out these work books once they are completed, but noit sure if that is possible either.

thanks for the help will give some of it a bash now.

got my first VBA course in a few weeks so hopefully after that I can reciprocate some of the help I have recieved on here to others.

[floricita]

Sorry. You're talking to a bit of a macro noob here, so apologies if this sounds like a stupid question.

But how does that macro or code, know what I want it to do specific to my needs? Because isn't it based on criteria that you have set it?

Sorry, that may have sounded confusing-basically i'm asking whether that what you have posted is the principle behind the idea which needs editing for my own personal needs, or that is exactly it?

[floricita]

Ok I tried running it and got a bug error at this juncture

"ws.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=MyArr(Itm)"

Any ideas on how to edit please?

the second macro worked perfectly. Although what I need to do now, is the same for other pieces of data and save them next to or in a specific order to the ones previously done. anyway to do this also?

Sorry I know that's a lot of questions.

[JBeaucaire]

How are you wanting to edit it? What needs to change?

[floricita]

Hi.

Sorry I am really bad at this at the moment and still trying desperately to learn.

The first one I have managed to get to work for my spreadsheets.

The second one works, but it produces it in a single new spreadsheet.

Ideally what I want is for it to go into an existing worksheet as a new tab. Then repeat for the other reports I have to do to put them all into one spreadsheet.

So in thoery I mean

I have reports A,D,C to run. They are all different but need to be compiled together in one spreadsheet by region, for examples sake, I have regions D,E and F.

I do stage one for each report giving me a seperate report for each region. That leaves me with 3 different reports per region. And I need all the reports for that region in one spreadsheet.

So I need Region D as one report containing reports ADC, then for Region E and so on.

Is this possible do you know?

[JBeaucaire]

Yes of course, everything is possible. I pointed you to my macros to get you started constructing one for yourself that does exactly what you need, published macros seldom work "out of the box" for every need, they need a bit of tweaking, or wholesale rewriting using the techniques you learn from the published macros.

It's meant to be a resource, not always a turn-key solution.

If you're looking for something done completely for you, you will need to post example workbooks with complete BEFORE/AFTER examples, something we can see clearly.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

[floricita]

Thanks.

Unfortunately it said a security token was missing when I tried this.

Thanks for your help though, I will endeavour with the challenge.