Macro to filter and copy visible data to a New Work Book

Hi guys,

I have got a file with 10 different Sheets with massive data . I want to create a light version of the file with the help of a Macro.
The Macro needs to do the following tasks,

1) Select Specific Sheet and unprotect the sheet.
2) Apply filter ,(I'm using Icons to filter the data) on Column D
3) Copy this filtered data (Only Visible Cells) into a new book Called "Light Version".

The above process repeats until 5 sheets are copied over into one file called "Light Version" and breaks the link file
to the source file. (Please note since there would be some defined names from the source file, the macro should delete all the
Names from the Name manager in the "Light Version file", before it can enable to break the link.

Any help is appreciated.

Thanks

Hi Dave

Attached please find a sample file. In the original file, there are various sheets but included in them will have 5 similar sheets as of my sample file.
I need a macro to select these 5 sheets where filter needs to be applied in column D (By Selecting Filter Icon(Green Tick)in each of these sheets and then copy over to a new file with visible data only.
Hope its clear!
Cheers

Hi Dave,

Did you get a chance to look at my thread. I have attached the sample file as requested.

Thanks

Hi Dave ,

The macro works only when I customise the excel save options to xlsx version . However I cannot save
this settings as it requires company's groups settings access.
Alternatively, I was able to customise these settings by copying over a dummy xlsx file within XLSTART folder in my user profile. Now whenever excel opens, it saves in 2007 excel version.

Despite the above changes, the macro is not able to call the "Light Version.xlsx" file which it creates at
the beginning of the macro.

Can you redirect this issue to someone who may help?

Thanks

Thanks Rory

I tried your code but it does not help. I have figured out what the problem is , which I have posted in my previous reply in the thread.
The VBA code works perfectly when I change the excel Save Options to 2007 version from its default setting as 2003. However I cannot save these settings in my user profile, as it needs group settings which only the IT Dept in our company can do it and as some people in the organisation have only 2003 version, these settings cannot be changed anytime soon. I'm curious is there something else which can I can tweak the code which stills allows me to create the excel file in 2007 and save the file.