Copier dans nouveauclasseur feuille sélectionnée avec checkbox

Fermé
apache084 Messages postés 2 Date d'inscription mardi 28 janvier 2014 Statut Membre Dernière intervention 1 février 2014 - 28 janv. 2014 à 19:36
apache084 Messages postés 2 Date d'inscription mardi 28 janvier 2014 Statut Membre Dernière intervention 1 février 2014 - 1 févr. 2014 à 14:28
Bonjour à tous, je suis tous nouveau dans la programmation je suis sous excel 2007 et je rencontre quelques petits soucis (en faite pour moi c'est un gros :-) )
Donc voilà j'ai une trentaine de checkbox correspondant chaqun a une feuille et je voudrais que lorsque je coche le checkbox, ça me copie la ou les feuilles cochés dans un nouveau classeur où je donnerais le chemin moi même. mais je galère si je met la totalité ca me donne une erreur "procédure trop grande " quand je les prend une par une ça fonctionne. j'ai essayé avec la fonction Call la première fonctionne et les deux suivante came copie le classeur entier. A l'aide SVP

Sub CommandButton65_Click()
Call CommandButton700
Call CommandButton701
Call CommandButton702
End Sub




Sub CommandButton700()
Application.ScreenUpdating = False
Dim objSaveBox As FileDialog
Dim sh As Shape
'
'
If EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
MsgBox "Vous devez selectionner au moins une feuille", vbInformation + vbOKOnly
Exit Sub

ElseIf EVCD = True And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées

Sheets(Array("evenement conduite")).Select ' selectionne la feuille
Sheets(Array("evenement conduite")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("evenement conduite").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("evenement conduite").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = True And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées

Sheets(Array("ANOT")).Select ' selectionne la feuille
Sheets(Array("ANOT")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("ANOT").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("ANOT").Select
Range("A1").Select



ElseIf EVCD = False And ANOT = False And ARPI = True And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées

Sheets(Array("ARPI")).Select
Sheets(Array("ARPI")).Copy
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("ARPI").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("ARPI").Select
Range("A1").Select


ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = True And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("ATRA")).Select ' selectionne la feuille
Sheets(Array("ATRA")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("ATRA").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("ATRA").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = True And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("AVEN")).Select ' selectionne la feuille
Sheets(Array("AVEN")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("AVEN").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("AVEN").Select
Range("A1").Select


ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = True And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("CBA")).Select ' selectionne la feuille
Sheets(Array("CBA")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("CBA").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("CBA").Select
Range("A1").Select


ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = True And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("C")).Select ' selectionne la feuille
Sheets(Array("C")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("C").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("C").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = True And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("MAIL")).Select ' selectionne la feuille
Sheets(Array("MAIL")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("MAIL").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("MAIL").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = True And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("MEQI")).Select ' selectionne la feuille
Sheets(Array("MEQI")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("MEQI").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("MEQI").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = True And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("OCAR")).Select ' selectionne la feuille
Sheets(Array("OCAR")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("OCAR").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("OCAR").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = True And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("VAIG")).Select ' selectionne la feuille
Sheets(Array("VAIG")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("VAIG").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("VAIG").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = True And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("VECA")).Select ' selectionne la feuille
Sheets(Array("VECA")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("VECA").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("VECA").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = True And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("VEVO")).Select ' selectionne la feuille
Sheets(Array("VEVO")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("VEVO").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("VEVO").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = True And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("PERS")).Select ' selectionne la feuille
Sheets(Array("PERS")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("PERS").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("PERS").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = True And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("DSEC")).Select ' selectionne la feuille
Sheets(Array("DSEC")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("DSEC").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("DSEC").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = True And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("REMA")).Select ' selectionne la feuille
Sheets(Array("REMA")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("REMA").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("REMA").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = True And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("ANSI")).Select ' selectionne la feuille
Sheets(Array("ANSI")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("ANSI").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("ANSI").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = True And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("CBA2")).Select ' selectionne la feuille
Sheets(Array("CBA2")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("CBA2").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("CBA2").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = True And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("DERA")).Select ' selectionne la feuille
Sheets(Array("DERA")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("DERA").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("DERA").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = True And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("MV")).Select ' selectionne la feuille
Sheets(Array("MV")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("MV").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("MV").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = True And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("RATO")).Select ' selectionne la feuille
Sheets(Array("RATO")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("RATO").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("RATO").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = True And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("S")).Select ' selectionne la feuille
Sheets(Array("S")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("S").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("S").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = True And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("VEFE")).Select ' selectionne la feuille
Sheets(Array("VEFE")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("VEFE").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("VEFE").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = True And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("AGSM")).Select ' selectionne la feuille
Sheets(Array("AGSM")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("AGSM").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("AGSM").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = True And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("CHEX")).Select ' selectionne la feuille
Sheets(Array("CHEX")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("CHEX").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("CHEX").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = True And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("FREP")).Select ' selectionne la feuille
Sheets(Array("FREP")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("FREP").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("FREP").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = True And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("JAMA")).Select ' selectionne la feuille
Sheets(Array("JAMA")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("JAMA").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("JAMA").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = True And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("MATA")).Select ' selectionne la feuille
Sheets(Array("MATA")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("MATA").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"



Sheets("MATA").Select
Range("A1").Select



ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = True And ICAB = False Then 'pas checkbox cochées
Sheets(Array("SECO")).Select ' selectionne la feuille
Sheets(Array("SECO")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("SECO").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("SECO").Select
Range("A1").Select

ElseIf EVCD = False And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = True Then 'pas checkbox cochées
Sheets(Array("ICAB")).Select ' selectionne la feuille
Sheets(Array("ICAB")).Copy 'la copie
ActiveSheet.Unprotect Password:="SoLeNe84"
Sheets("ICAB").Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"

Sheets("ICAB").Select
Range("A1").Select




End If
Set objSaveBox = Application.FileDialog(msoFileDialogSaveAs)

With objSaveBox


'Définit le type de fichier par défaut:
'(la valeur 4 Permet de spécifier les classeurs "Excel 97-2003" lorsque vous êtes dans Excel 2007)
.FilterIndex = 4

'Affiche la boîte de dialogue
.Show
'Enregistre
.Execute
End With
ActiveWindow.Close 'ferme le nouveau classeur
Unload UserForm20
Sheets("accueil").Select
Application.ScreenUpdating = True

CommandButton701
End Sub

Sub CommandButton701()
Application.ScreenUpdating = False
Dim objSaveBox As FileDialog
Dim sh As Shape


ElseIf EVCD = True And ANOT = True And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("evenement conduite", "ANOT")).Select
Sheets(Array("evenement conduite", "ANOT")).Copy
Sheets("evenement conduite").Unprotect Password:="SoLeNe84"
Sheets("ANOT").Unprotect Password:="SoLeNe84"
Sheets(Array("evenement conduite", "ANOT")).Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select

Sheets("evenement conduite").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"
Range("A1").Select
ActiveWindow.SmallScroll Down:=-135

Sheets("ANOT").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("A1").Select
ActiveWindow.SmallScroll Down:=-135

Sheets("evenement conduite").Select

ElseIf EVCD = True And ANOT = False And ARPI = True And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("evenement conduite", "ARPI")).Select
Sheets(Array("evenement conduite", "ARPI")).Copy
Sheets("evenement conduite").Unprotect Password:="SoLeNe84"
Sheets("ARPI").Unprotect Password:="SoLeNe84"
Sheets(Array("evenement conduite", "ARPI")).Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select

Sheets("evenement conduite").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"
Range("A1").Select
ActiveWindow.SmallScroll Down:=-135

Sheets("ARPI").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("A1").Select
ActiveWindow.SmallScroll Down:=-135

Sheets("evenement conduite").Select

ElseIf EVCD = True And ANOT = False And ARPI = False And ATRA = True And AVEN = False And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("evenement conduite", "ATRA")).Select
Sheets(Array("evenement conduite", "ATRA")).Copy
Sheets("evenement conduite").Unprotect Password:="SoLeNe84"
Sheets("ATRA").Unprotect Password:="SoLeNe84"
Sheets(Array("evenement conduite", "ATRA")).Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select

Sheets("evenement conduite").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"
Range("A1").Select
ActiveWindow.SmallScroll Down:=-135

Sheets("ATRA").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("A1").Select
ActiveWindow.SmallScroll Down:=-135

Sheets("evenement conduite").Select

ElseIf EVCD = True And ANOT = False And ARPI = False And ATRA = False And AVEN = True And CBA = False And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("evenement conduite", "AVEN")).Select
Sheets(Array("evenement conduite", "AVEN")).Copy
Sheets("evenement conduite").Unprotect Password:="SoLeNe84"
Sheets("AVEN").Unprotect Password:="SoLeNe84"
Sheets(Array("evenement conduite", "AVEN")).Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select

Sheets("evenement conduite").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"
Range("A1").Select
ActiveWindow.SmallScroll Down:=-135

Sheets("AVEN").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("A1").Select
ActiveWindow.SmallScroll Down:=-135

Sheets("evenement conduite").Select

ElseIf EVCD = True And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = True And C = False And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("evenement conduite", "CBA")).Select
Sheets(Array("evenement conduite", "CBA")).Copy
Sheets("evenement conduite").Unprotect Password:="SoLeNe84"
Sheets("CBA").Unprotect Password:="SoLeNe84"
Sheets(Array("evenement conduite", "CBA")).Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select

Sheets("evenement conduite").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"
Range("A1").Select
ActiveWindow.SmallScroll Down:=-135

Sheets("CBA").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("A1").Select
ActiveWindow.SmallScroll Down:=-135

Sheets("evenement conduite").Select

ElseIf EVCD = True And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = True And MAIL = False And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("evenement conduite", "C")).Select
Sheets(Array("evenement conduite", "C")).Copy
Sheets("evenement conduite").Unprotect Password:="SoLeNe84"
Sheets("C").Unprotect Password:="SoLeNe84"
Sheets(Array("evenement conduite", "C")).Select
Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select

Sheets("evenement conduite").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"
Range("A1").Select
ActiveWindow.SmallScroll Down:=-135

Sheets("C").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO150").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("A1").Select
ActiveWindow.SmallScroll Down:=-135

Sheets("evenement conduite").Select

ElseIf EVCD = True And ANOT = False And ARPI = False And ATRA = False And AVEN = False And CBA = False And C = False And MAIL = True And MEQI = False And OCAR = False And VAIG = False And VECA = False And VEVO = False And PERS = False And DSEC = False And REMA = False And ANSI = False And CBA2 = False And DERA = False And MV = False And RATO = False And S = False And VEFE = False And AGSM = False And CHEX = False And FREP = False And JAMA = False And MATA = False And SECO = False And ICAB = False Then 'pas checkbox cochées
Sheets(Array("evenement conduite", "MAIL")).Select <
A voir également:

1 réponse

apache084 Messages postés 2 Date d'inscription mardi 28 janvier 2014 Statut Membre Dernière intervention 1 février 2014
1 févr. 2014 à 14:28
Bonjour, voilà cette macro va très bien mais une fois le nouveau classeur créer avec les copie, les bouton ont etais supprimer que su la premiere feuille. comment faire pour les suivante, j'ai vu qu'il fallait faire une boucle mai je n'y arrive pas. Merci d'avance

Private Sub CommandButton65_Click()
Application.ScreenUpdating = False
Dim Ctl As Control
Dim Idx As Long
Dim arrFeuilles() As String
Dim objSaveBox As FileDialog
Dim chemin As Variant
Dim sh As Shape
'On passe chaque contrôle du UserForm
For Each Ctl In Me.Controls
'Si c'est un checkbox
If TypeOf Ctl Is msforms.CheckBox Then
'et qu'il est coché
If Ctl.Value = True Then
'on met dans un tableau (Array) qui va servir pour sélectionner les feuilles
ReDim Preserve arrFeuilles(Idx)
arrFeuilles(Idx) = Ctl.Tag
Idx = Idx + 1
End If
End If
Next

ThisWorkbook.Sheets(arrFeuilles).Select
ThisWorkbook.Sheets(arrFeuilles).Copy
ActiveSheet.Unprotect Password:="SoLeNe84"

Cells.Copy
Cells.PasteSpecial xlPasteValues ' on fait un copier-coller valeur pour supprimer les formules, et donc les liaisons
Range("A1").Select
For Each sh In ActiveSheet.Shapes
If Not sh.Name Like "cible" And Not sh.Name Like "logo" Then sh.Delete
Next sh
Range("A1:AO110").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="SoLeNe84", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="SoLeNe84"


Range("D1").Select
ActiveWindow.SmallScroll Down:=-135


Set objSaveBox = Application.FileDialog(msoFileDialogSaveAs)

With objSaveBox

chemin = (Range("a2").Value & "_" & Range("A3").Text)
'enregistre sous
'Définit le type de fichier par défaut:
'(la valeur 4 Permet de spécifier les classeurs "Excel 97-2003" lorsque vous êtes dans Excel 2007)
.FilterIndex = 4

'Affiche la boîte de dialogue
.Show
'Enregistre
.Execute
End With
ActiveWindow.Close 'ferme le nouveau classeur
Unload UserForm20
Sheets("accueil").Select
Application.ScreenUpdating = True

End Sub
0