Copier dans nouveauclasseur feuille sélectionnée avec checkbox
Fermé
apache084
Messages postés2Date d'inscriptionmardi 28 janvier 2014StatutMembreDernière intervention 1 février 2014
-
28 janv. 2014 à 19:36
apache084
Messages postés2Date d'inscriptionmardi 28 janvier 2014StatutMembreDerniè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"
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:
Copier dans nouveauclasseur feuille sélectionnée avec checkbox
apache084
Messages postés2Date d'inscriptionmardi 28 janvier 2014StatutMembreDerniè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
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"
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