Macro or Formula to extract data

Hi Guys,

I need some help with a rather large spreasheet that i'm working on. I have attached the worksheet for your reference. What i'm trying to do is extract data from the sheet called P&L and dump it onto the work sheet called Data Extract.

I need a formula or macro that will sense if there's any data in any rows for columns "U" to "AF" inclusive and dump all the data for the relevant months into the Data Extract worksheet. I would also like the macro to copy and paste the corresponding data label for that row from column B.

For example, row 148 has some data and the next set of data is in row 164 and 165. I have copied and pasted that info into the Data Extract worksheet as I would like it. If possible, I would like to exclude the rows with totals from dumping onto the DATA EXTRACT worksheet.

The purpose of the macro is to save me the time of looking at every single row and coping and pasting information into the data extract worksheet. There are over 700 rows and there's over 50 of these worksheets.

Any help would be highly appreciated.

Cheers!

Hi

Try the following macro.

Please Login or Register  to view this content.

You should clean out the existing data in rows 3-5 in Data Extract before running.

HTH

rylo

Brilliant Rylo. Very helpful as always ... much appreciated mate!

One small glitch is that it only returns those rows that have data starting in Jan (Column "U") so for example if costs dont start flowing on till Feb or later months, Jan will be blank and it wont pick that row up even if there's data for the rest of the months.

I have attached the revised file with the macro and the relevant buttons in the worksheet called "Data Extract". Is it possible for you to edit the macro so that when u run the Extract Data macro, it comes back to the "Data Extract" worksheet rather than the "P&L" worksheet?

Cheers for your help!

Hi

Try this one.

Please Login or Register  to view this content.

rylo

Hi

Try this

Please Login or Register  to view this content.

rylo

hey rylo,

That macro seems to be coming up with an error. I had a few changes to the data that I need to extract:

- I need to extract 2007 data now as well. This is tricky however because I only need to extract data for the months of July 07 to Dec o7 and then the full year for 2008. So all I am interested in is columns "K to P" for 2007 and columns "U to AF" for 2008.

- For 2007, I only need to extract those rows that have data in columns "K to P" so that edit to the macro where I referred 2008 data to column "AG" which holds the total, will not work for 2007.

I have attached the new file for your reference.

Cheers mate!!!

ps: sorry for the trouble

Hi

What is going to be the trigger to brring in the data? A non 0 number in column Q? A non 0 number in column AG? Non 0 in both? What happens if (say) column Q is non 0, but there is nothing from July - Dec (row 187)?


rylo

Hi Rylo,

Column AG is fine as the trigger for 2008 data but not for Jul-Dec 2007 too.

I'm not sure as to what we can have as the trigger for it all. Maybe format the data to get rid of all the unnecessary columns and just lave in Jun-Dec 07 and Jan to Decv 08 and have a total to the right of the 18 months and use that as the trigger.

Its a bit of a tricky one!

Hi

So that would leave in all the rows, including the ones you previously wanted removed???

rylo