Sub MAJEXPT()

Application.ScreenUpdating = False

ActiveSheet.Calculate

Sheets("ENTETE").Select

titre = "Balances à auditer"

MsgVerif = MsgBox("Avez-vous collées (collage spécial valeur) vos balances dans l'onglet consobalance ?", vbYesNo, titre)

If MsgVerif = vbYes Then

Else: Exit Sub

End If

Sheets("BALANCE").Visible = True

Sheets("VERSION BALANCE").Visible = True

Sheets("BALANCE (2)").Visible = True

Sheets("ENTETE").Select

Dim nomfichier As String

nomfichier = Range("p3")

reponse1 = MsgBox("Est-ce votre premier moulinage ?", vbYesNo)

If reponse1 = vbYes Then

reponse2 = MsgBox("Etes vous sûr(e)?", vbYesNo)

If reponse2 = vbYes Then

Windows(nomfichier).Activate

Sheets("VERSION BALANCE").Visible = True

Sheets("version balance").Select

Rows("2:2").Select

Selection.ClearContents

Range("A4:CL2029").Select

Selection.ClearContents

Range("A1").Select

Sheets("entete").Select

Range("p5") = 0

End If

Else

'si déjà mouliné recopie des affectations

Sheets("BALANCE").Select

Range("A1:X2029").Select

Selection.Copy

Sheets("BALANCE (2)").Select

Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("BALANCE (2)").Visible = False

End If

Sheets("ENTETE").Select

Dim numerobalance As Integer

numerobalance = Range("p5")

Range("p5") = numerobalance + 1

Application.Run Macro:="MAJBAL"

'Application.Run Macro:="copierbalance"

Application.Run Macro:="basedonnees"

'Application.Run Macro:="TRIBALANCE"

'Application.Run Macro:="TRIBALANCE2"

'Sheets("menu").Select

'If Cells(12, 4) = "O" Then

'Application.Run Macro:="menu.XLS!copier2"

'Else

'Application.Run Macro:="menu.xls!suppimmo"

'Application.Run Macro:="menu.xls!copier4"

'End If

'Application.Run Macro:="copier3"

'Windows("menu.xls").Activate

'Sheets("menu").Select

'If Cells(12, 4) = "O" Then

'Application.Run Macro:="menu.XLS!CAPCCA"

'Else

'End If

With Application

.Calculation = xlAutomatic

.MaxChange = 0.001

End With

'Windows("menu.XLS").Activate

'Sheets("menu").Select

Sheets("BALANCE (2)").Visible = False

Application.ScreenUpdating = True

Sheets("entete").Select

MsgBox "Tri de la balance terminé.", vbExclamation, "Message"

Dim numeroarchive As Integer

numeroarchive = Range("s20")

reponse = MsgBox("Voulez-vous archiver cette balance ?", vbYesNo)

If reponse = vbYes Then

Windows(nomfichier).Activate

Sheets("balance").Select

Range("A5:C2029").Select

Selection.Copy

Sheets("VERSION BALANCE").Select

Cells(4, numeroarchive).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Cells(2, numeroarchive).Select

'ActiveCell.FormulaR1C1 = "=MAINTENANT()"

ActiveCell.FormulaR1C1 = "=NOW()"

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("entete").Select

MsgBox "Balance archivée dans l'onglet VERSION BALANCE.", vbExclamation, "Message"

End If

End Sub

'MAJBAL Macro

' Macro enregistrée le 24/01/1996 par OLIVIER LALANDE

'

'

Sub MAJBAL()

Sheets("ENTETE").Select

Dim nomfichier As String

nomfichier = Range("p3")

Windows(nomfichier).Activate

Sheets("CONSOBALANCE").Select

Columns("M:XFD").Select

Selection.EntireColumn.Hidden = True

Columns("L:XFD").Select

Selection.EntireColumn.Hidden = False

Range("p1").Select

Selection.Copy

Range("q1").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Range("q1").Select

Application.CutCopyMode = False

Selection.Copy

Range("A2:A2000").Select

Selection.PasteSpecial Paste:=xlAll, Operation:=xlDivide, SkipBlanks:= _

False, Transpose:=False

Range("r1").Select

Selection.Copy

Range("s1").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Range("s1").Select

Application.CutCopyMode = False

Selection.Copy

Range("d2:d2000").Select

Selection.PasteSpecial Paste:=xlAll, Operation:=xlDivide, SkipBlanks:= _

False, Transpose:=False

Range("t1").Select

Selection.Copy

Range("u1").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Range("u1").Select

Application.CutCopyMode = False

Selection.Copy

Range("g2:g2000").Select

Selection.PasteSpecial Paste:=xlAll, Operation:=xlDivide, SkipBlanks:= _

False, Transpose:=False

Range("v1").Select

Selection.Copy

Range("w1").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Range("w1").Select

Application.CutCopyMode = False

Selection.Copy

Range("j2:j2000").Select

Selection.PasteSpecial Paste:=xlAll, Operation:=xlDivide, SkipBlanks:= _

False, Transpose:=False

Windows(nomfichier).Activate

Sheets("CONSOBALANCE").Select

Range("M8").Select

ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotCache.Refresh

Sheets("CONSOBALANCE").Select

Range("t4:y2029").Select

Selection.Copy

Range("z4").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("z4").Select

Range("z4:ae2029").AdvancedFilter Action:=xlFilterInPlace, _

CriteriaRange:=Range("Y1:aB2"), Unique:=False

Range("z4:ae2029").Select

Selection.ClearContents

Range("z4").Select

ActiveCell.FormulaR1C1 = "COMPTE"

Range("aa4").Select

ActiveCell.FormulaR1C1 = "LIBELLE"

Range("ab4").Select

ActiveCell.FormulaR1C1 = "N"

Range("ac4").Select

ActiveCell.FormulaR1C1 = "N-1"

Range("ad4").Select

ActiveCell.FormulaR1C1 = "N-2"

Range("ae4").Select

ActiveCell.FormulaR1C1 = "N-3"

Range("ae802").Select

ActiveSheet.ShowAllData

Range("z4").Select

Range("z4:ae2029").AdvancedFilter Action:=xlFilterInPlace, _

CriteriaRange:=Range("aJ1:aN2"), Unique:=False

Range("z4:ae2029").Select

Selection.ClearContents

Range("z4").Select

ActiveCell.FormulaR1C1 = "COMPTE"

Range("aa4").Select

ActiveCell.FormulaR1C1 = "LIBELLE"

Range("ab4").Select

ActiveCell.FormulaR1C1 = "N"

Range("ac4").Select

ActiveCell.FormulaR1C1 = "N-1"

Range("ad4").Select

ActiveCell.FormulaR1C1 = "N-2"

Range("ae4").Select

ActiveCell.FormulaR1C1 = "N-3"

Range("ae802").Select

ActiveSheet.ShowAllData

Range("z4").Select

Range("z4:ae2029").AdvancedFilter Action:=xlFilterInPlace, _

CriteriaRange:=Range("Ac1:Ae3"), Unique:=False

Range("z4:ae2029").Select

Selection.ClearContents

Range("z4").Select

ActiveCell.FormulaR1C1 = "COMPTE"

Range("aa4").Select

ActiveCell.FormulaR1C1 = "LIBELLE"

Range("ab4").Select

ActiveCell.FormulaR1C1 = "N"

Range("ac4").Select

ActiveCell.FormulaR1C1 = "N-1"

Range("ad4").Select

ActiveCell.FormulaR1C1 = "N-2"

Range("ae4").Select

ActiveCell.FormulaR1C1 = "N-3"

Range("ae255").Select

ActiveSheet.ShowAllData

Range("z4").Select

Range("z4:ae2029").AdvancedFilter Action:=xlFilterInPlace, _

CriteriaRange:=Range("Ao1:As3"), Unique:=False

Range("z4:ae2029").Select

Selection.ClearContents

Range("z4").Select

ActiveCell.FormulaR1C1 = "COMPTE"

Range("aa4").Select

ActiveCell.FormulaR1C1 = "LIBELLE"

Range("ab4").Select

ActiveCell.FormulaR1C1 = "N"

Range("ac4").Select

ActiveCell.FormulaR1C1 = "N-1"

Range("ad4").Select

ActiveCell.FormulaR1C1 = "N-2"

Range("ae4").Select

ActiveCell.FormulaR1C1 = "N-3"

Range("ae255").Select

ActiveSheet.ShowAllData

Range("z5:ae2029").Select

Selection.Sort Key1:=Range("z5"), Order1:=xlAscending, Header:= _

xlGuess, OrderCustom:=1, MatchCase:=False, _

Orientation:=xlTopToBottom

Range("z5:ae2029").Select

Selection.Copy

Windows(nomfichier).Activate

Sheets("balance").Visible = True

Sheets("balance").Select

Range("A5").Select

Selection.PasteSpecial Paste:=xlValues

Sheets("CONSOBALANCE").Select

Columns("M:XFD").Select

Selection.EntireColumn.Hidden = True

Range("a2").Select

Sheets("balance").Select

Range("A5").Select

End Sub

'COPIERBALANCE Macro

' Macro enregistrée le 25/07/1995 par BRUNO GERVEREAU

'

'

Sub COPIERBALANCE()

'Fenêtres(nomfichier)

'Feuilles("BALANCE")

Sheets("ENTETE").Select

Dim nomfichier As String

nomfichier = Range("p3")

Sheets("balance").Select

Range("A3:F2000").Select

Selection.Copy

Sheets("TRICOMPTES").Select

Range("A19").Select

Selection.PasteSpecial Paste:=xlValues

Sheets("TRICOMPTES (2)").Select

Range("A19").Select

Selection.PasteSpecial Paste:=xlValues

End Sub

Sub basedonnees()

'Fenêtres(nomfichier)

Sheets("ENTETE").Select

Dim nomfichier As String

nomfichier = Range("p3")

Sheets("BALANCE").Select

Range("A5:F2000").Select

Selection.Copy

Range("G5").Select

Selection.PasteSpecial Paste:=xlValues

Application.CutCopyMode = False

ActiveWorkbook.Names.Add Name:="DATABASE", RefersToR1C1:= _

"=BALANCE!R4C7:R2000C12"

Range("M4:V2000").Select

ActiveWorkbook.Names.Add Name:="DATABASE2", RefersToR1C1:= _

"=BALANCE!R4C13:R2004C22"

End Sub

' TRIBALANCE Macro

' Macro enregistrée le 25/07/1995 par BRUNO GERVEREAU

'

'

Sub TRIBALANCE()

'Fenêtres(nomfichier).Activer

Sheets("ENTETE").Select

Dim nomfichier As String

nomfichier = Range("p3")

Sheets("TRICOMPTES").Select

Range("A15").Select

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("G22:H26"), CopyToRange:=Range("G27:L27") _

, Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("M24:N26"), CopyToRange:=Range("M27:R27") _

, Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("S22:T24"), CopyToRange:=Range("S27:X27") _

, Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("Y24:Z26"), CopyToRange:=Range("Y27:AD27") _

, Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("AE24:AF26"), CopyToRange:=Range( _

"AE27:AJ27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("AK24:AL26"), CopyToRange:=Range( _

"AK27:AP27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("AQ24:AR26"), CopyToRange:=Range( _

"AQ27:AV27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("AW24:AX26"), CopyToRange:=Range( _

"AW27:BB27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("BC24:BD26"), CopyToRange:=Range( _

"BC27:BH27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("BI24:BJ26"), CopyToRange:=Range( _

"BI27:BN27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("BO24:BP26"), CopyToRange:=Range( _

"BO27:BT27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("BU24:BV26"), CopyToRange:=Range( _

"BU27:BZ27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("CA24:CB26"), CopyToRange:=Range( _

"CA27:CF27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("CG24:CH26"), CopyToRange:=Range( _

"CG27:CL27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("CM24:CN26"), CopyToRange:=Range( _

"CM27:CR27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("CS24:CT26"), CopyToRange:=Range( _

"CS27:CX27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("CY24:CZ26"), CopyToRange:=Range( _

"CY27:DD27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("DE24:DF26"), CopyToRange:=Range( _

"DE27:DJ27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("DK24:DL26"), CopyToRange:=Range( _

"DK27:DP27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("DQ24:DR26"), CopyToRange:=Range( _

"DQ27:DV27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("DW24:DX26"), CopyToRange:=Range( _

"DW29:EB29"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("EC23:ED26"), CopyToRange:=Range( _

"EC29:EH29"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("EI22:EJ26"), CopyToRange:=Range( _

"EI27:EN27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("EO24:EP26"), CopyToRange:=Range( _

"EO27:ET27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("EU24:EV25"), CopyToRange:=Range( _

"EU27:EZ27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("FA21:FB26"), CopyToRange:=Range( _

"FA27:FF27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("FG24:FH25"), CopyToRange:=Range( _

"FG27:FL27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("FM24:FN25"), CopyToRange:=Range( _

"FM27:FR27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("FS24:FT26"), CopyToRange:=Range( _

"FS27:FX27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("FY24:FZ26"), CopyToRange:=Range( _

"FY27:GD27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("GE24:GF26"), CopyToRange:=Range( _

"GE27:GJ27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("GK24:GL26"), CopyToRange:=Range( _

"GK27:GP27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("GQ24:GR26"), CopyToRange:=Range( _

"GQ27:GV27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("GW24:GX26"), CopyToRange:=Range( _

"GW27:HB27"), Unique:=False

Range("A19:F2000").Select

Selection.ClearContents

End Sub

'

' COPIER3 Macro

' Macro enregistrée le 07/08/1995 par BRUNO GERVEREAU

'

'

Sub COPIER3()

Sheets("ENTETE").Select

Dim nomfichier As String

nomfichier = Range("p3")

Windows(nomfichier).Activate

Sheets("balance").Select

Range("W1:IT2000").Select

Selection.Clear

Sheets("tricomptes").Select

Range("G21:HB600").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("BALANCE").Select

Range("W1").Select

Selection.PasteSpecial Paste:=xlValues

Sheets("TRICOMPTES").Select

Range("G27:HB500").Select

Selection.Clear

Range("A3").Select

Sheets("BALANCE").Select

Range("A3").Select

End Sub

Sub exportonglet()

'

' exportonglet Macro

' Macro enregistrée le 16/09/2005 par

'

'

Application.DisplayAlerts = False

Application.ScreenUpdating = False

ActiveSheet.Calculate

Sheets("ENTETE").Select

Dim expert As String

expert = Range("p3")

Range("AT1:AT33,AV1").Select

Selection.ClearContents

Dim synthese As String

If Range("aq1") = "expert" Then

UserForm3.Show

Unload UserForm3

synthese = "SYNTHESE"

Else

UserForm4.Show

Unload UserForm4

synthese = "SYNTHESE (2)"

End If

If Range("av1") = "TOUS" Then

Windows(expert).Activate

Sheets("ENTETE").Select

For I = 1 To Range("AL1")

If Range("aq1") = "expert" Then

nomonglet = Cells(I, 36)

Else

nomonglet = Cells(I, 39)

End If

If Cells(I, 37) = "FAUX" Then

GoTo a:

Else

Sheets("entete").Select

cheminnomfichier1 = Range("I4") + "\" + nomonglet + ".xls"

Sheets(nomonglet).Visible = True

Sheets(nomonglet).Select

Sheets(nomonglet).Copy

Dim nomfichier As String

nomfichier = nomonglet + ".xls"

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path + "\" + nomonglet + ".xls", FileFormat _

:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _

False, CreateBackup:=False

'Intégration entete, synthèse,balance,analyse financière,revue analytique et feuille de travail

Windows(expert).Activate

Sheets("ENTETE").Copy Before:=Workbooks(nomfichier).Sheets(nomonglet)

Windows(expert).Activate

Sheets("ENTETE").Select

Cells.Select

Selection.Copy

Windows(nomfichier).Activate

Cells.Select

Selection.UnMerge

Cells.Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

' Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

Windows(expert).Activate

Sheets("balance").Copy Before:=Workbooks(nomfichier).Sheets(nomonglet)

Windows(expert).Activate

Sheets("balance").Select

Cells.Select

Selection.Copy

Windows(nomfichier).Activate

Cells.Select

ActiveSheet.Paste

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

Windows(expert).Activate

Sheets("F DE W REF.").Visible = True

Sheets("F DE W REF.").Copy After:=Workbooks(nomfichier).Sheets(nomonglet)

Windows(expert).Activate

Sheets("F DE W REF.").Select

Cells.Select

Selection.Copy

Windows(nomfichier).Activate

Cells.Select

ActiveSheet.Paste

Windows(expert).Activate

Sheets("ANALYSE FINANCIERE").Copy Before:=Workbooks(nomfichier).Sheets(nomonglet)

Windows(expert).Activate

Sheets("ANALYSE FINANCIERE").Select

Cells.Select

Selection.Copy

Windows(nomfichier).Activate

Cells.Select

ActiveSheet.Paste

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

Windows(expert).Activate

Sheets("REVUE ANALYTIQUE").Copy Before:=Workbooks(nomfichier).Sheets(nomonglet)

Windows(expert).Activate

Sheets("REVUE ANALYTIQUE").Visible = True

Sheets("REVUE ANALYTIQUE").Select

Cells.Select

Selection.Copy

Windows(nomfichier).Activate

Cells.Select

ActiveSheet.Paste

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

Windows(expert).Activate

Sheets("feuilles maitresse").Copy After:=Workbooks(nomfichier).Sheets("F DE W REF.")

Windows(expert).Activate

Sheets("feuilles maitresse").Visible = True

Sheets("feuilles maitresse").Select

Cells.Select

Selection.Copy

Windows(nomfichier).Activate

Cells.Select

ActiveSheet.Paste

Windows(expert).Activate

Sheets(synthese).Copy Before:=Workbooks(nomfichier).Sheets("balance")

Windows(expert).Activate

Sheets(synthese).Visible = True

Sheets(synthese).Select

Cells.Select

Selection.Copy

Windows(nomfichier).Activate

Cells.Select

ActiveSheet.Paste

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

If nomonglet = "C-STOCKS" Or nomonglet = "F-STOCK" Then

Windows(expert).Activate

Sheets("ratios").Copy After:=Workbooks(nomfichier).Sheets(nomonglet)

Windows(nomfichier).Activate

Sheets("ratios").Visible = True

Sheets("ratios").Select

Cells.Select

Selection.Copy

Windows(nomfichier).Activate

Cells.Select

ActiveSheet.Paste

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

Windows(expert).Activate

Sheets("QUEST & CI STOCKS").Copy After:=Workbooks(nomfichier).Sheets(nomonglet)

Windows(nomfichier).Activate

Sheets("QUEST & CI STOCKS").Visible = True

Sheets("QUEST & CI STOCKS").Select

Cells.Select

Selection.Copy

Windows(nomfichier).Activate

Cells.Select

ActiveSheet.Paste

Else

End If

If nomonglet = "I-POS" Or nomonglet = "H-PERSONNEL" Then

Windows(expert).Activate

Sheets("PARTICIPATION").Copy After:=Workbooks(nomfichier).Sheets(nomonglet)

Windows(nomfichier).Activate

Sheets("PARTICIPATION").Visible = True

Sheets("PARTICIPATION").Select

Cells.Select

Selection.Copy

Windows(nomfichier).Activate

Cells.Select

ActiveSheet.Paste

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

Else

End If

If nomonglet = "6-7" Then

Windows(expert).Activate

Sheets("RESULT EXPLOIT").Copy After:=Workbooks(nomfichier).Sheets(nomonglet)

Windows(nomfichier).Activate

Sheets("RESULT EXPLOIT").Visible = True

Sheets("RESULT EXPLOIT").Select

Cells.Select

Selection.Copy

Windows(nomfichier).Activate

Cells.Select

ActiveSheet.Paste

Windows(expert).Activate

Sheets("RESULT FI").Copy After:=Workbooks(nomfichier).Sheets(nomonglet)

Windows(nomfichier).Activate

Sheets("RESULT FI").Visible = True

Sheets("RESULT FI").Select

Cells.Select

Selection.Copy

Windows(nomfichier).Activate

Cells.Select

ActiveSheet.Paste

Windows(expert).Activate

Sheets("RESULT EXCEPT").Copy After:=Workbooks(nomfichier).Sheets(nomonglet)

Windows(nomfichier).Activate

Sheets("RESULT EXCEPT").Visible = True

Sheets("RESULT EXCEPT").Select

Cells.Select

Selection.Copy

Windows(nomfichier).Activate

Cells.Select

ActiveSheet.Paste

Else

End If

If nomonglet = "J-ETA" Or nomonglet = "L-IMPOTS ET TAXES" Then

Windows(expert).Activate

Sheets("PLAFONNEMENT TP").Copy After:=Workbooks(nomfichier).Sheets(nomonglet)

Windows(nomfichier).Activate

Sheets("PLAFONNEMENT TP").Visible = True

Sheets("PLAFONNEMENT TP").Select

Cells.Select

Selection.Copy

Windows(nomfichier).Activate

Cells.Select

ActiveSheet.Paste

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

Windows(expert).Activate

Sheets("RECAP TVA").Copy After:=Workbooks(nomfichier).Sheets(nomonglet)

Windows(nomfichier).Activate

Sheets("RECAP TVA").Visible = True

Sheets("RECAP TVA").Select

Cells.Select

Selection.Copy

Windows(nomfichier).Activate

Cells.Select

ActiveSheet.Paste

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

Else

End If

Sheets(nomonglet).Select

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

ActiveSheet.Calculate

ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _

"=BALANCE!R4C1:R2029C6"

ActiveWorkbook.Names.Add Name:="Base_de_données", RefersToR1C1:= _

"=BALANCE!R4C1:R2029C6"

ActiveWorkbook.Names.Add Name:="DATABASE2", RefersToR1C1:= _

"=BALANCE!R4C13:R2029C22"

Sheets("ENTETE").Select

ActiveWindow.SelectedSheets.Visible = False

Sheets(synthese).Select

ActiveWindow.SelectedSheets.Visible = False

Sheets("REVUE ANALYTIQUE").Visible = True

Sheets("REVUE ANALYTIQUE").Select

ActiveWindow.SelectedSheets.Visible = False

Sheets("feuilles maitresse").Visible = True

Sheets("feuilles maitresse").Select

ActiveWindow.SelectedSheets.Visible = False

Sheets(nomonglet).Select

Range("a1").Select

ActiveWorkbook.Save

ActiveWindow.Close

Windows(expert).Activate

Sheets("entete").Select

Range("a1").Select

a:

End If

Next I

'-------------------------------OU 1 OU PLUSIEURS CYCLES

Else

For J = 1 To Range("AL1")

nomonglet1 = Cells(J, 46)

If nomonglet1 = "" Then GoTo x:

Windows(expert).Activate

Sheets("entete").Select

Dim nomfichier2 As String

cheminnomfichier2 = Range("I4") + "\" + nomonglet1 + ".xls"

Dim cheminexport1 As String

cheminexport1 = Range("I4")

Dim nomfichier1 As String

nomfichier1 = nomonglet1 + ".xls"

Sheets(nomonglet1).Select

Sheets(nomonglet1).Copy

Dim nomfichier3 As String

cheminnomfichier3 = "c:\temp\export\" + nomonglet1

nomfichier = nomonglet1 + ".xls"

If cheminexport1 = "" Then

ActiveWorkbook.SaveAs Filename:=cheminnomfichier3, FileFormat _

:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _

False, CreateBackup:=False

Else

ActiveWorkbook.SaveAs Filename:=cheminnomfichier2, FileFormat _

:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _

False, CreateBackup:=False

End If

'Intégration entete, synthèse,balance,analyse financière,revue analytique et feuille de travail

Windows(expert).Activate

Sheets("ENTETE").Copy Before:=Workbooks(nomfichier1).Sheets(nomonglet1)

Windows(expert).Activate

Sheets("ENTETE").Select

Cells.Select

Selection.Copy

Windows(nomfichier1).Activate

Cells.Select

Selection.UnMerge

Cells.Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

' Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

Windows(expert).Activate

Sheets("balance").Copy Before:=Workbooks(nomfichier1).Sheets(nomonglet1)

Windows(expert).Activate

Sheets("balance").Select

Cells.Select

Selection.Copy

Windows(nomfichier1).Activate

Cells.Select

ActiveSheet.Paste

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

ActiveSheet.Calculate

Windows(expert).Activate

Sheets("F DE W REF.").Visible = True

Sheets("F DE W REF.").Copy After:=Workbooks(nomfichier1).Sheets(nomonglet1)

Windows(expert).Activate

Sheets("F DE W REF.").Select

Cells.Select

Selection.Copy

Windows(nomfichier1).Activate

Cells.Select

ActiveSheet.Paste

ActiveSheet.Calculate

Windows(expert).Activate

Sheets("ANALYSE FINANCIERE").Copy Before:=Workbooks(nomfichier1).Sheets(nomonglet1)

Windows(expert).Activate

Sheets("ANALYSE FINANCIERE").Select

Cells.Select

Selection.Copy

Windows(nomfichier1).Activate

Cells.Select

ActiveSheet.Paste

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

ActiveSheet.Calculate

Windows(expert).Activate

Sheets("REVUE ANALYTIQUE").Copy Before:=Workbooks(nomfichier1).Sheets(nomonglet1)

Windows(expert).Activate

Sheets("REVUE ANALYTIQUE").Visible = True

Sheets("REVUE ANALYTIQUE").Select

Cells.Select

Selection.Copy

Windows(nomfichier1).Activate

Cells.Select

ActiveSheet.Paste

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

ActiveSheet.Calculate

Windows(expert).Activate

Sheets("feuilles maitresse").Visible = True

Sheets("feuilles maitresse").Copy After:=Workbooks(nomfichier1).Sheets("F DE W REF.")

Windows(expert).Activate

Sheets("feuilles maitresse").Select

Cells.Select

Selection.Copy

Windows(nomfichier1).Activate

Cells.Select

ActiveSheet.Paste

ActiveSheet.Calculate

Windows(expert).Activate

Sheets("SYNTHESE").Copy Before:=Workbooks(nomfichier1).Sheets("balance")

Windows(expert).Activate

Sheets(synthese).Select

Cells.Select

Selection.Copy

Windows(nomfichier1).Activate

Cells.Select

ActiveSheet.Paste

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

ActiveSheet.Calculate

If nomonglet1 = "F-STOCKS" Or nomonglet1 = "C-STOCKS" Then

Windows(expert).Activate

Sheets("ratios").Copy After:=Workbooks(nomfichier1).Sheets(nomonglet1)

Windows(expert).Activate

Sheets("ratios").Visible = True

Sheets("ratios").Select

Cells.Select

Selection.Copy

Windows(expert).Activate

Cells.Select

ActiveSheet.Paste

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

ActiveSheet.Calculate

Windows(expert).Activate

Sheets("QUEST & CI STOCKS").Copy After:=Workbooks(nomfichier1).Sheets(nomonglet1)

Windows(nomfichier1).Activate

Sheets("QUEST & CI STOCKS").Visible = True

Sheets("QUEST & CI STOCKS").Select

Cells.Select

Selection.Copy

Windows(expert).Activate

Cells.Select

ActiveSheet.Paste

ActiveSheet.Calculate

Else

If nomonglet1 = "I-POS" Or "H-PERSONNEL" Then

Windows(expert).Activate

Sheets("PARTICIPATION").Copy After:=Workbooks(nomfichier).Sheets(nomonglet1)

Windows(nomfichier1).Activate

Sheets("PARTICIPATION").Visible = True

Sheets("PARTICIPATION").Select

Cells.Select

Selection.Copy

Windows(expert).Activate

Cells.Select

ActiveSheet.Paste

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

ActiveSheet.Calculate

Else

If nomonglet1 = "6-7" Then

Windows(expert).Activate

Sheets("RESULT EXPLOIT").Copy After:=Workbooks(nomfichier1).Sheets(nomonglet1)

Windows(expert).Activate

Sheets("RESULT EXPLOIT").Select

Cells.Select

Selection.Copy

Windows(expert).Activate

Cells.Select

ActiveSheet.Paste

Windows(nomfichier1).Activate

Sheets("RESULT FI").Copy After:=Workbooks(nomfichier1).Sheets(nomonglet1)

Windows(expert).Activate

Sheets("RESULT FI").Select

Cells.Select

Selection.Copy

Windows(expert).Activate

Cells.Select

ActiveSheet.Paste

Windows(expert).Activate

Sheets("RESULT EXCEPT").Copy After:=Workbooks(nomfichier1).Sheets(nomonglet1)

Windows(expert).Activate

Sheets("RESULT EXCEPT").Select

Cells.Select

Selection.Copy

Windows(expert).Activate

Cells.Select

ActiveSheet.Paste

ActiveSheet.Calculate

Else

If nomonglet1 = "J-ETA" Or "L-IMPOTS ET TAXES" Then

Windows(expert).Activate

Sheets("PLAFONNEMENT TP").Copy After:=Workbooks(nomfichier1).Sheets(nomonglet1)

Windows(expert).Activate

Sheets("PLAFONNEMENT TP").Select

Cells.Select

Selection.Copy

Windows(nomfichier1).Activate

Cells.Select

ActiveSheet.Paste

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

Windows(expert).Activate

Sheets("RECAP TVA").Copy After:=Workbooks(nomfichier1).Sheets(nomonglet1)

Windows(nomfichier1).Activate

Sheets("RECAP TVA").Select

Cells.Select

Selection.Copy

Windows(expert).Activate

Cells.Select

ActiveSheet.Paste

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

ActiveSheet.Calculate

Else

End If

End If

End If

End If

Sheets(nomonglet1).Select

Cells.Replace What:="[" + expert + "]", Replacement _

:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

ActiveSheet.Calculate

Windows(nomfichier1).Activate

ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _

"=BALANCE!R4C1:R2029C6"

ActiveWorkbook.Names.Add Name:="Base_de_données", RefersToR1C1:= _

"=BALANCE!R4C1:R2029C6"

ActiveWorkbook.Names.Add Name:="DATABASE2", RefersToR1C1:= _

"=BALANCE!R4C13:R2029C22"

Sheets(nomonglet1).Select

ActiveSheet.Calculate

Sheets("ENTETE").Select

ActiveWindow.SelectedSheets.Visible = False

Sheets(synthese).Select

ActiveWindow.SelectedSheets.Visible = False

Sheets("REVUE ANALYTIQUE").Visible = True

Sheets("REVUE ANALYTIQUE").Select

ActiveWindow.SelectedSheets.Visible = False

Sheets("feuilles maitresse").Visible = True

Sheets("feuilles maitresse").Select

ActiveWindow.SelectedSheets.Visible = False

Sheets(nomonglet1).Select

Range("a1").Select

ActiveSheet.Calculate

ActiveWorkbook.Save

ActiveWindow.Close

Windows(expert).Activate

ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _

"=BALANCE!R4C1:R2029C6"

ActiveWorkbook.Names.Add Name:="Base_de_données", RefersToR1C1:= _

"=BALANCE!R4C1:R2029C6"

x:

Next J

Sheets("entete").Select

Range("a1").Select

End If

Sheets("feuilles maitresse").Visible = False

Sheets("F DE W REF.").Visible = False

MsgBox "Export terminé.", vbExclamation, "Message"

'1

Sheets("entete").Select

Range("a1").Select

'End If

MsgBox "Documents enregistrés : " & ThisWorkbook.Path

End Sub

Sub insertion67()

'

' insertion67 Macro

' Macro enregistrée le 18/02/2003 par CERA

'

'

Counter = 1

Sheets("6-7").Activate

While Not Range("A" & Counter) = "N°"

Counter = Counter + 1

Wend

Range("A" & Counter).Select

Selection.RemoveSubtotal

Counter1 = Counter

Sheets("6-7").Activate

While Not Range("A" & Counter1) = "P1"

Counter1 = Counter1 + 1

Wend

Range("a" & Counter).Select

Range(Cells(Counter, 1), Cells(Counter1 - 4, 7)).AdvancedFilter Action:=xlFilterInPlace, _

CriteriaRange:=Range("X1:Y2"), Unique:=False

Range(Cells(Counter + 1, 1), Cells(Counter1 - 4, 7)).Select

Selection.ClearContents

ActiveSheet.ShowAllData

Range(Cells(Counter, 1), Cells(Counter1 - 4, 7)).Select

ActiveWorkbook.Worksheets("6-7").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("6-7").Sort.SortFields.Add Key:=Range(Cells(Counter + 2, 1), Cells(Counter1 - 4, 1)) _

, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("6-7").Sort

.SetRange Range(Cells(Counter + 2, 1), Cells(Counter1 - 4, 7))

.Header = xlGuess

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Counter2 = Counter

Sheets("6-7").Activate

While Not Range("A" & Counter2) = ""

Counter2 = Counter2 + 1

Wend

Range("a" & Counter).Select

Selection.Subtotal GroupBy:=9, Function:=xlSum, TotalList:=Array(3, 4, 5), _

Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Range("F" & Counter + 1).Select

ActiveCell.FormulaR1C1 = _

"=IF(ISERR(+RC[-3]/RC[-2]-1)=TRUE,"""",+RC[-3]/RC[-2]-1)"

Range("f" & Counter + 1).Select

Selection.AutoFill Destination:=Range(Cells(Counter + 2, 6), Cells(Counter1 - 4, 6)), Type:=xlFillDefault

Range("B" & Counter).Select

Cells.Find(What:="TOTAL Général", After:=ActiveCell, LookIn:=xlFormulas, LookAt _

:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

False).Activate

ActiveCell.Range(Cells(Counter + 2, 1), Cells(Counter1 - 4, 8)).Select

Selection.Delete Shift:=xlUp

Range("A" & Counter + 2).Select

Range(Cells(Counter + 2, 1), Cells(Counter2, 8)).Select

Dim a As Long

For a = Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1

If Left(Cells(a, "i"), 5).String = "Total" _

Then Cells(a, 1).EntireRow.Select

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent6

.TintAndShade = 0.799981688894314

.PatternTintAndShade = 0

End With

Selection.Font.Bold = True

Cells(a, 1).Select

ActiveCell.FormulaR1C1 = "=RC[8]"

Next a

End Sub

Sub exportongletsynthese()

'

' exportonglet Macro

' Macro enregistrée le 16/09/2005 par Lalande

'

'

Application.DisplayAlerts = False

Sheets("ENTETE").Select

Dim nomfichier As String

nomfichier = Range("p3")

Sheets("SYNTHESE").Select

Dim cheminnomfichier As String

cheminnomfichier = "c:\temp\" + "expertsynthese.xls"

Workbooks.Add

ActiveWorkbook.SaveAs Filename:=cheminnomfichier

For I = 1 To 13

Windows(nomfichier).Activate

Sheets("ENTETE").Select

nomonglet = Cells(I, 34)

Sheets(nomonglet).Copy Before:=Workbooks("expertsynthese.xls").Sheets(1)

Windows(nomfichier).Activate

Sheets(nomonglet).Select

Cells.Select

Selection.Copy

Windows("expertsynthese.xls").Activate

Cells.Select

ActiveSheet.Paste

Cells.Select

With Selection

.WrapText = False

.Orientation = 0

.AddIndent = False

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

'Application.Run Macro:="EXPERT.XLS!Synthese"

Cells(50, 1).Select

For J = 50 To 150

If Left(CStr(Cells(J, 1)), 13) = "Chiffres clés" Then

Cells(J, 1).Select

ActiveCell.Offset(0, 0).Range("A1:J250").Select

Selection.Copy

Workbooks(nomfichier).Activate

Sheets("feuilles maitresse").Select

Cells(I * 251, 1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

End If

Workbooks("expertsynthese.xls").Activate

Next

Next

Workbooks("expertsynthese.xls").Activate

ActiveWorkbook.SaveAs Filename:=cheminnomfichier

ActiveWindow.Close

Windows(nomfichier).Activate

Sheets("synthese").Select

MsgBox "Mise à jour terminée.", vbExclamation, "Message"

End Sub

Sub TRIBALANCE2()

Sheets("ENTETE").Select

Dim nomfichier As String

nomfichier = Range("p3")

Sheets("TRICOMPTES (2)").Select

Range("A15").Select

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("G22:H23"), CopyToRange:=Range("G27:L27") _

, Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("M24:N25"), CopyToRange:=Range("M27:R27") _

, Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("S22:T23"), CopyToRange:=Range("S27:X27") _

, Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("Y24:Z25"), CopyToRange:=Range("Y27:AD27") _

, Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("AE24:AF25"), CopyToRange:=Range( _

"AE27:AJ27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("AK24:AL25"), CopyToRange:=Range( _

"AK27:AP27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("AQ24:AR25"), CopyToRange:=Range( _

"AQ27:AV27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("AW24:AX25"), CopyToRange:=Range( _

"AW27:BB27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("BC24:BD25"), CopyToRange:=Range( _

"BC27:BH27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("BI24:BJ25"), CopyToRange:=Range( _

"BI27:BN27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("BO24:BP25"), CopyToRange:=Range( _

"BO27:BT27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("BU24:BV25"), CopyToRange:=Range( _

"BU27:BZ27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("CA24:CB25"), CopyToRange:=Range( _

"CA27:CF27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("CG24:CH25"), CopyToRange:=Range( _

"CG27:CL27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("CM24:CN25"), CopyToRange:=Range( _

"CM27:CR27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("CS24:CT25"), CopyToRange:=Range( _

"CS27:CX27"), Unique:=False

Range("a20:F2000").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("CY24:CZ25"), CopyToRange:=Range( _

"CY27:DD27"), Unique:=False

Range("A19:F3507").Select

Selection.ClearContents

End Sub