Sélection de feuilles

On veut pouvoir sélectionner une ou des feuilles pour changer leurs propriétés ou leur contenu.

Sélectionner une feuille appelée "Toto"

sheets("toto").select

sélectionner la feuille d'un autre classeur

L'activation est requise avant la sélection, vous devez écrire :
    workbooks("Classeur2").Sheets("Feuil3").activate
avant de pouvoir écrire :
    workbooks("Classeur2").sheets("Feuil3").activate

ou bien
     workbooks("Classeur2").activate
avant de pouvoir écrire
      activeworkbook.sheets("feuil3").activate

mais de toute façon, vous n'êtes pas obligé de "sélectionner la feuille" pour agir dessus, l'activation suffit, et, à contrario de la sélection, elle fonctionne même si la feuille est (normalement) masquée.

si un autre classeur est actif, vous pouvez toujours travailler sur le classeur d'où est lancée la macro en utilisant le mot clé "This workbook" : thisworkbook.sheets("feuil2").select

Sélectionner plusieurs feuilles

Vous pouvez sélectionner plusieurs feuilles en même temps manuellement ou par VBA.
VBA vous permettra de sélectionner toutes les feuilles ou des feuilles particulières, répondant par exemple à une ou des conditions.

Sélection manuelle : maintenez enfoncée la touche CTRL et cliquez sur les onglets des feuilles à sélectionner. Vous êtes en mode "Groupe de Travail" et cela est affiché dans la barre de titres d'Excel. Toute action (ou presque) effectuée sur la feuille active est répercutée sur les autres feuilles sélectionnées. Seules certains effacements, les créations de certains objets sont impossibles dans ce mode

Sélection par VBA : vous pouvez sélectionner des feuilles directement ou boucler sur toutes les feuilles ou des feuilles données afin d'agir dessus, voire de les sélectionner sous condition pour passer en mode "Groupe de Travail" et par exemple toutes les imprimer.

Voilà une méthode pour sélectionner deux feuilles Toto & Tata, indifféremment du type
    sheets(array("toto","tata")).select

'sélection de deux feuilles de calcul
    worksheets(array("toto","tata")).select

'sélection d'une feuille graphique
charts("Graph1").select
    une fois sélectionné, vous pouvez le modifier ainsi :
    selection.charttype=52 ou ' ainsi activechart.charttype = xlColumnStacked

'sélection de deux feuilles graphiques
    sheets(array("Graph2", "Graph1")).select
    charts(Array("Graph1", "Graph2")).Select

'sélection de feuilles dont les noms figurent dans les cellules A1:A5
    sheets(application.transpose(range("A1:A5"))).select

Il n'est toutefois pas nécessaire de sélectionner une feuille pour la modifier

exemple de manipulation

     sheets("Feuil1").activate 'activation de la feuille nécessaire dans ce cas
     sheets("Feuil1").name="Feuille renommée"
     sheets("Feuil1").visible=xlhidden

     'cela peut aussi s'écrire de la manière suivante
     with sheets("Feuil1")
       .name="Feuillerenommee")
        .visible=xlhidden
      end with

autre exemple
     with worksheets(array("toto","tata")) 'activation non requise d'une des feuilles dans ce cas
           .item(1).name="Feuil1"
            .item(2).name="Feuil2"
     end with


Manipulation à l'aide de boucles, sélection sous condition

'Excel peut boucler sur des collections d'objets, il sera donc possible de lui demander de boucler sur toutes les feuilles de calcul, les feuiilles sélectionnées, un groupe de feuilles, etc. Pour d'autres infos sur les types de boucles, voir xl_vba_2.htm.

'Manipulation des feuilles sélectionnées
     for each ws in activeworkbook.windows(1).selectedsheets
          msgbox ws.name
     next ws

'Autre manipulation d'un groupe défini de feuilles
'Déprotection de feuilles
    Groupe = Array("Feuil1", "Feuil2", "Feuil3")
    for each Feuille in sheets(Groupe)
          Feuille.unprotect ("chat")
    next Feuille
    Sheets(Groupe).select

'Manipulation, renommage et sélection sous condition
    for i = 1 To worksheets.count
        if sheets(i).name <> "Feuil1" or sheets(i).name <> "Feuil2" then
            sheets(i).name = "Feuille en position" & i
            sheets(i).select replace:=false
            j=j+1
        end if
    next i
    if j>0 then msgbox "Des feuilles ont été renommées et sélectionnées"

'Boucler sur toutes les feuilles de calcul pour les lister
    for rw =1 to worksheets.count
        if cells(rw,1)<>"" then worksheets(rw).name = cells(rw,1)
    next

Exemples de sélections plus compliquées

Sélectionner les feuilles où A1 contient 1000 (d'après E.J.), Lundi 07/02/2000

 

Sub typeA()
dim prov() As Variant
nbFeuil = 0
For i = 0 To sheets.Count - 1
     If sheets(i + 1).range("A1").value = 1000 Then
          redim preserve prov(nbFeuil)
          prov(nbFeuil) = sheets(i + 1).Name
          nbFeuil = nbFeuil + 1
     end If
Next
sheets(prov).Select
end Sub

Sélectionner la feuille du mois en cours
en supposant que les mois soit présentés de la forme "Janvier", "Février","Mars"
     Worksheets(Format(Date,"mmmm")).Select

Masquer, cacher des feuilles
     worksheets(array("feuil1","feuil2","feuil3")).Visible=xlHidden
     worksheets("feuil1").Visible=xlHidden
     'Inspirer vous également de ce message là : news

Si une feuille est masquée en apposant la valeur xlVeryHidden à sa propriété visible, cette feuille sera réaffichable que par le biais d'une macro VBA.

Création de feuilles

La création de feuilles se fait à l'aide de la méthode Add appliquée à l'objet Sheets.

Pour un simple ajout de feuille de calcul, il faut utiliser l'instruction ci-contre : sheets.add
Pour ajouter et en même temps nommer la feuille : sheets.add.name="NouvelleFeuille"

La méthode Add applicable à cet objet Sheets a des paramètres permettant différentes choses.

Paramètres

After & before : pour le positionnement de la feuille créée après ou avant une autre

Vous pouvez les positionner après une feuille en spécifiant son nom ou sa position

sheets.add before:= sheets("nomfeuille")
sheets.add after:=sheets("nomfeuille")
sheets.add after:=sheets(2) 'positionnement après la seconde feuille (tout type de feuille compris)
sheets.add after:=sheets(sheets.count) : 'positionnement en dernière position

Count : spécification du nombre de feuilles créées

sheets.add count:=3

Type : pour indiquer quel est le type de la feuille à créer

Il y a 5 types de feuilles, par défaut la méthode add créée une feuille de calcul (xlWorksheet)

xlChart : une feuille graphique
xlDialogSheet : une feuille Boîte de dialogue héritée des versions antérieures d'excel
xlExcel4IntlMacroSheet : une feuille de macros XL4
xlExcel4MacroSheet : une feuille de macros XL4
xlWorksheet : une feuille de calcul standard

Dans les exemples, vous verrez comment utiliser les paramètres ( nomduparam:=valeurparam).
Mais vous verrez aussi que l'on peut omettre leur nom tant que l'on les renseigne dans l'ordre suivant : Before, After, Count, Type

création de deux feuilles de calcul en dernière position
        sheets.add after:=sheets(sheets.count), count:=2

création d'une feuille avant la feuille active
        worksheets.add(before:=activesheet).name = "TheName"

création d'une feuille type graphique
        sheets.add , , , xlChart
        ou encore sheets.add , , , Excel.XlSheetType.xlchart

Exemples de création plus alambiquées

Créer une feuille de calcul & demander à l'utilisateur de saisir le nom :

sheets.add type:="Worksheet"
activesheet.name = inputbox("Enter Sheet name:")

Création & nommage de plusieurs feuilles

Pour deux feuilles
     sheets.add count:=2
     sheets(sheets.count-1).name="avantdernierefeuille"
     sheets(sheets.count).name="dernierefeuille"

Pour plusieurs feuilles
       sub demo
       dim x as integer
       x = 3 'trois feuilles créées
       sheets.add count:=x
       for i = x to 0 step -1
       sheets(sheets.count - i).name = "toto" & (sheets.count - i)
       next i
       end sub

news news

De: Laurent longre <laurent.longre@free.fr>, Objet: Re: Créer des feuilles, Date : samedi 18 mars 2000 13:59

dim I As Integer
application.screenupdating = false
worksheets.add sheets(1), count:=12
for I = 1 to 12
sheets(I).name = application.Proper(format(I * 30, "mmmm"))
next I

Autre méthode, en faisant appel à la liste prédéfinie qui contient les mois

for i = 1 to 12
activeworkbook.sheets.add
activesheet.name = application.GetCustomListContents(4)(i)
next i

'ce code ajoute autant de feuilles qu'il faut pour arriver à 20
Sub ajoutFeuilles()
nbVoulu = 20
nbActuel = ActiveWorkbook.sheets.Count
ActiveWorkbook.Worksheets.Add Count:=nbVoulu - nbActuel
For i = 1 To nbVoulu
sheets(i).Name = "Feuil" & Format(sheets(i).Index, "00")
Next i
end Sub

Sub créerRésultat()
Dim dep : j = 5 : dep = Array(1989, 1991, 1993, 1996, 1999)
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:\WINDOWS\Bureau\Résultat.xls" : ActiveSheet.Name = dep(1)
For i = 2 To j
Sheets.Add : ActiveSheet.Name = dep(i)
Next i
End Sub

Sub Inserer_Renommer()
Dim i As Integer, Plage As Range
Set Plage = Sheets(1).Range("a1:a5")
If Sheets.Count < Plage.Cells.Count Then
Do
Sheets.Add after:=Sheets(Sheets.Count)
Loop Until Sheets.Count = Plage.Cells.Count
End If
For i = 1 To 5
Sheets(i).Name = Format(Plage.Cells(i), "dd mmmm yyyy")
Next i
End Sub

application.sheetsinworkbook = X. [boîte Outils/options].
Cette information est contenue à l'adresse suivante de la base de registre : HKEY_CURRENT_USER\Software\Microsoft\Office\8.0\Excel\Microsoft Excel\Defsheets

Copie de feuilles

Copier la feuille 3 après elle-même

worksheets("Feuil3").copy after:=worksheets("Feuil3")
Cette méthode copie aussi les objets et leurs codes, sinon essayez ceci

à l'aide des paramètres after et before, vous pouvez spécifier après quelle feuille du même classeur ou d'un autre classeur vous désirez copier (ou sur le même principe déplacer les feuilles)
worksheets(array("Sheet1","Sheet2")).copy after:=workbooks("classeur2").worksheets("Feuil3")

si vous ne spécifiez pas ces paramètres, un nouveau classeur contenant ces feuilles sera créé :
worksheets(array("Sheet1","Sheet2")).copy

La méthode VBA pour déplacer une feuille est la méthode Move.
Si la ou les feuilles déplacées ont des liaisons avec d'autres classeurs, les liaisons seront conservées.

sheets("Feuil3").Move Before:=sheets(2)
sheets("Feuil1").Move Before:=workbooks("Classeur2").sheets(1) 'Déplacement dans un autre classeur

workbooks("otherbook.xls").worksheets("Sheet1").range("B6:C31").copy _ destination:=workbooks("targetbook.xls").worksheets("Sheet6").range("R34")

Sub Copie_feuille_liaison()
worksheets("Sheet1").copy after:=sheets(sheets.count)
Set sh = activesheet : sh.range("A1").select
worksheets("Sheet1").usedrange.copy
sh.pastespecial link:=true
worksheets("Sheet1").cells.copy
sh.range("A1").pastespecial xlformats
end Sub

Sub ConvertirFichiersEnFeuilles()
On Error GoTo gesterreur
Dim VarListeFichiers As Variant, VarFichier As Variant, WkClasseur As Workbook, WkFinal As Workbook, WsFeuille As Worksheet

VarListeFichiers = Application.GetOpenFilename(filefilter:="Classeurs eXceL,*.xls", Title:="Choisissez les Classeurs à récupérer", MultiSelect:=True)
If VarType(VarListeFichiers) = vbBoolean Then MsgBox "Abandon !" : Exit Sub    'pour identifier le bouton annuler
Set WkFinal = Workbooks.Add 'générer le classeur final

For Each VarFichier In VarListeFichiers
   Set WkClasseur = Workbooks.Open(FileName:=VarFichier)
   For Each WsFeuille In WkClasseur.Worksheets
       WsFeuille.Move before:=WkFinal.Worksheets(1)
   Next WsFeuille
   WkClasseur.Close savechanges:=False
Next VarFichier
Exit Sub
gesterreur:
'classeur vide
If Err.Number = -2147221080 Then
Resume Next
End If

End Sub

Sub nomFeuilles()
nbOnglets = ActiveWorkbook.Sheets.Count
ReDim tablo(nbOnglets, 2)
For n = 1 To nbOnglets
    tablo(n, 1) = Sheets(n).Name
    tablo(n, 2) = Sheets(n).Range("A1")
Next 'Collecte des valeurs et noms de feuille
Workbooks.Add 'ajout d'un classeur
For n = 1 To nbOnglets
    Cells(n, 1) = tablo(n, 1)
    Cells(n, 2) = tablo(n, 2)
Next 'Traitement des feuilles
End Sub

Sub SheetCopy(Source As Worksheet, Before As Worksheet)
Dim CmdB1 As CommandButton, CmdB2 As CommandButton
Dim Dest As Worksheet, I As Integer
Source.Copy Before
Set Dest = Before.Previous
For I = 1 To Source.OLEObjects.Count
    With Source.OLEObjects(I)
        If TypeOf .Object Is CommandButton Then
            Set CmdB1 = .Object
            Set CmdB2 = Dest.OLEObjects(I).Object
            CmdB2.Name = CmdB1.Name
        End If
    End With
Next I
End Sub

Sub Test()
SheetCopy Sheets(1), Before:=Sheets(2)
End Sub

Problèmes lors de copie de feuilles

<<Couramment j'effectue une copie de la feuille du mois M pour obtenir M+1. Lors de l'enregistrement, survient un plantage d'excel avec le message "violation dans le module excel.exe ..." Après quelques recherches, j'ai abouti à la conclusion suivante : La proprièté (name) dans visual lié à excel est égale à Feuil411111111111111111112111111.>>

=> Pour éviter cela, au lieu de copier les feuilles "à la chaîne" (Feuil1 dupliquée en Feuil2, elle-même dupliquée en Feuil3 etc.), il faudrait que tu copies toujours la même feuille de base, par exemple Feuil1.
Si le nom de code de Feuil4 est "Feuil4", par exemple :
- Copie de Feuil4 en 'Feuil4 (2)' => nouveau nom de code = "Feuil41"
- Copie de 'Feuil4 (2)' en 'Feuil4 (3)' => nouveau nom de code = "Feuil411"
- Copie de 'Feuil4 (3)' en 'Feuil4 (4)' => nouveau nom de code = "Feuil4111"

A chaque nouvelle copie, VBA ajoute un "1" au précédent nom de code, voilà comment, par exemple, au bout de 10 copies enchaînées tu te retrouves avec un CodeName contenant "Feuil41111111111". Alors que si tu copies toujours la même feuille de départ, Feuil4, les nouveaux noms de code des feuilles dupliquées seront "Feuil41", "Feuil42", "Feuil43" et ainsi de suite (incrémentation du numéro de copie), donc il n'y a plus de risque de tomber sur cette erreur.

Suppression de feuille

'utliser la commande ci-dessous en préalable à la suppression, à rétablir éventuellement après
application.displayalerts = false

Sub NoMessageOndelete()
On Error Resume Next
dim x As Integer
x = InputBox("Which Sheet to delete")
Application.DisplayAlerts = false
thisworkbook.Worksheets(x).delete
Application.DisplayAlerts = True
end Sub

XL2000/ XL96 : Sheets(Format$(DateAdd("m", -13, Date), "mm-yy")).Delete XL 5/95 (je crois que DateAdd n'existe pas pour ces versions) : Sheets(Format$(DateSerial(Year(Date), Month(Date) - 13, 1), "mm-yy")).Delete

Protection & Masquage

activesheet.Protect password = "VotreMotdePasse", DrawingObjects:=true, contents:=true, scenarios:=true
activesheet.Unprotect password = "VotreMotdePasse"

Nom de la feuille/onglet

Nom de feuilles dans une cellule

Function SheetName()
application.volatile 'pour que la formule se recalcule
SheetName = Application.Caller.Worksheet.Name
End Function

Function FirstSheetName()
application.volatile 'pour que la formule se recalcule
FirstSheetName = Sheets(1).Name
End Function

Function LastSheetName()
application.volatile 'pour que la formule se recalcule
LastSheetName = Sheets(Sheets.Count).Name
End Function

Une formule de feuille de calcul
="Mois d"&SI(OU(MINUSCULE(STXT(CELLULE("filename";A2);TROUVE("]"; CELLULE("filename";A2))+1;1))={"a";"o"});"'";"e ")&STXT(CELLULE("filename";A2);TROUVE("]";CELLULE("filename";A2))+1;32)

Une fonction personnalisée en VBA est préférable
Function NOMFEUILLE()
Dim Mois As String
Application.Volatile
Mois = Application.Caller.Worksheet.Name
NOMFEUILLE = "Mois d" & IIf(LCase$(Left$(Mois, 1)) _
Like "[a,o]", "'", "e ") & Mois
End Function
'écrire =NOMFEUILLE() dans une cellule

Renommer les feuilles Renommer les feuilles

Sub RenommerLesFeuilles()
dim LaFeuille As Worksheet, i As Integer : i = 1
On Error Resume Next
For each LaFeuille In Worksheets
LaFeuille.Name = "TaFeuille" & i
i = i + 1
Next
end Sub

dim DObj As New DataObject
DObj.GetFromClipboard
ActiveSheet.Name = DObj.gettext(1)

Sub renommer()
Dim Dates, Onglet As String
Dates = ActiveSheet.Range("A1").Value
Onglet = Format(Dates, "dd-mm-yyyy")
If Not IsEmpty(Dates) Then
    On Error GoTo mon_message
    ActiveSheet.Name = [Onglet]
End If
Exit Sub
mon_message:
attention = MsgBox("La feuille existe déjà", vbOKOnly, "Attention")
End Sub

Liste des feuilles Lister les feuilles d'un classeur dans une nouvelle feuille

Sub ListerFeuilles()
with thisworkbook
.sheets.Add
For i = 2 To .sheets.Count
range("A" & i - 1).value = .sheets(i).Name
Next i
end with
end Sub
voir aussi un peu plus haut, partie Copie

'Vous pouvez utiliser cette fonction en tant que fonction dans une formule matricielle
Function AllSheetNames()
Dim Arr() As String
Dim I as Integer
Redim Arr(Sheets.Count-1)
For I = 0 To Sheets.Count - 1
Arr(i) = Sheets(I+1).Name
Next I
AllSheetNames = Arr ' return a row array OR
AllSheetNames = Application.Worksheetfunction.Transpose(Arr)
' return a column array
End Function

premier exemple

Private Sub Workbook_Open()
dim sht As Worksheet
For each sht In Worksheets
    If Sht.Name <> "dontUseThisSheet" Then
      sheets("Directory").ComboBox1.AddItem sht.Name
    end If
Next sht
end Sub

Private Sub UserForm_Initialize()
dim sht As Worksheet
For each sht In ActiveWorkbook.Worksheets
ComboBox1.AddItem sht.Name
Next sht
end Sub

C'est possiblement de manière assez simple sur XL 2000 en utilisant l'ADO (pour cela, inscrivez une référence à "Microsoft ActiveX Data Objects 2.1 Library" et "Microsoft ADO Ext. 2.1 for DDL and Security", à travers le menu outils/références de VBE)

Dim cnn As New ADODB.Connection, cat As New ADOX.Catalog, tbl As ADOX.Table

cnn.Open "Provider=MSDASQL.1;Data Source=" & "Excel Files;Initial Catalog=c:\book1.xls"
cat.ActiveConnection = cnn
For Each tbl In cat.Tables
MsgBox Left$(tbl.Name, Len(tbl.Name) - 1)
Next tbl
Set cat = Nothing
cnn.Close
Set cnn = Nothing

Autres La feuille active est vide ?

l'instruction ci-dessous contrôle si aucune valeur,quelle qu'elle soit, est présente dans l'ensemble des celllues
if application.counta(activesheet.cells) = 0 then msgbox "Sheet is empty"

Exemple : news Trier des feuilles selon un ordre déterminée dans une liste, ici selon le nom des mois : news Tri des feuilles en fonction de la valeur d'une de leur cellules : news

Ci-dessous, voilà une fonction qui teste l'existence d'une feuille (indifféremment de son type).

function IssheetstillThere(SheetName) as Boolean
dim obj as Object
On Error Resume Next
Set obj = ActiveWorkbook.sheets(SheetName)
IssheetstillThere = True
If Err <> 0 Then
IssheetstillThere = false
end If
end function

On peut imaginer l'utiliser pour détecter si des feuilles ont été effacées ou non, comme le permet la procédure évènementielle ci-dessous, reposant sur l'évènement worksheet_activate. Pour un autre exemple de cette problématique, regardez la partie Suppression plus haut.

Private Sub Worksheet_Activate()
If IsSheetStillThere("Detail1") Then
'code here
end if
If IsSheetStillThere("Detail2") Then
'code here
end if
'etc.........................
End Sub

Sub RechercheMot()
mot = InputBox("Mot à rechercher ?")
For feuille = 1 To sheets.Count
sheets(feuille).Select : Set trouvé1 = Cells.find(What:=mot)
   If Not trouvé1 Is Nothing Then
      trouvé1.Activate
      étiq:
      If MsgBox("suivant?", 4) = vbNo Then Exit Sub
          Set trouvé2 = Cells.findNext(After:=ActiveCell)
      If trouvé2.Column <> trouvé1.Column Or trouvé2.Row <> trouvé1.Row Then
         trouvé2.Activate
         GoTo étiq
      End If
    End If
Next feuille
End Sub