Copy data from one sheet too different new sheets based on a filter

Hello,

I have a base datasheet and I would like to extract from this datasheet with a button, data which is filtered. These filters are fixed, so nobody have to select what should be filtered. With one push of the button the new files shoud be generated.

I have added an example file. Within this file I would like to have 3 new files based on the value in column 3 "LOCATIE". At the moment you can find the following data in this file: E01 /E01-MP1 / E01-MP2.

So I would like to have 3 files named E01 / E01-MP1 and E01-MP2.

The second filter would be the data which is the column 2 "DRAAD_ID". If this cell is "empty" or "xxxx", this row should not be exported.

Can some body help me with this?

Best Regards

Maybe something like this? I'm sure it could be written better but this should be simple enough to follow and adapt where necessary.

It will save the three 'filtered' workbooks to the same folder the main workbook is in.

Please Login or Register  to view this content.

BSB

That is nice to start from. Thanks for that.

I Have one more question. I would like to paste the cells but only the values.

With the .Sheets(1).paste the formulas stay in the new sheet and that is not ok.

So how can I adapt this paste function?

Regards

Hello,

I have changed the coded that was proved by BSB. But now I have on other problem. I would like to copy the format of the range and only the values which are in the cells. I don't need the formulas any more.

I have attached a copy. On the second sheet, at the bottom, you can find the button to activate the code to copy and create the new documents.

If I want to copy the format of the selected range. The format is copied to a range bigger than the range that was selected. So how can I copy the values and the formats of the selected range.

Regards

Paste the whole lot in, formulas and all then use the below to convert it all to values rather than formulas.

Please Login or Register  to view this content.

BSB

BSB,

Thanks for the reply, with that piece of code it does what I wnat it to do.

Just another question. I the first part of code you gave me, you have 4 fixed filter. Now I would like to check each range before it is copied. If the range is empty, I don't want it to be copied.

How do I check if a range is empty after we set the autofilter?

Regards