Hello guys,

First off all, I'm a newbee as Vba developper so please be indulgent.

I'm working on a macro that could extract the actual filtering (Autofilter) sheets from my workbook to a new one without formulas, macro enabled BUT keeping format (merge cells).


Got difficulties with 3 things :
[*]Sheet 1 contain Autofilter, Iwould like to ewport just the display on screen data.[*]Sheet 1 contain formatting like merge cells and colors.[*]Sheet 1 contain a "C1" Cell obtain by concatenate text and value obtain by Autofilter Sub (see below)

C1 formula :

"VAGUE "&(RIGHT(AutoFilter_Criteria(A7);(LEN(AutoFilter_Criteria(A7))-FIND("=";AutoFilter_Criteria(A7)))))&" du "&TEXT(VLOOKUP((RIGHT(AutoFilter_Criteria(A7);(LEN(AutoFilter_Criteria(A7))-FIND("=";AutoFilter_Criteria(A7)))));Créneaux!A2:B20;2;FALSE);"[$-40C]jjjj j mmmm aaaa")
It display, "Vague " then Autofilter criteria contain in "A7" following by a vlookupvalue for the date of the autofilter criteria match. (Match between Criteria name and date are contain in a sheet named "Créneaux"

Example C1 : VAGUE 3 du dimanche 3 février 2013

Code Autofilter :

Please Login or Register  to view this content.
And here my code, i'm blocking on

Please Login or Register  to view this content.
The following error appear :: Run-time error '1004': Cannot change part of a merge cell.


So How do you copy paste keeping values and formats merge cells ?



Best Regards and thanks by advance.

Sorry for english mistakes