Créer plusieurs private sub dans workbook

Le 18/04/2020 à 11:56

Bonjour à tous,

Je cherche à faire en sorte que dès l'ouverture du fichier certaines actions se fassent seules. Comme par exemple inscrire la dernière date de la mise à jour dans une cellule spécifique, ou copier les données de certains onglets pour venir les recopier dans d'autres onglets afin d'avoir un historique et de faire des calculs.

Je ne sais pas si l'intention est bonne, mais pour cela j'ai voulu créer plusieurs private sub dans le même workbook. Forcément dès le 2ème private sub ça a planté. Je vous mets le code que j'ai renseigné. Il vient principalement d'un code créé manuellement pour un onglet, donc sans doute avec trop de manipulation. Je vous joins le fichier… help !!

Merci d'avance pour votre aide et bonne journée...

Private Sub Workbook_Open()

Sheets("TOTAL").Select

Range("B2").Select

Selection.Copy

Range("B3").Select

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

:=False, Transpose:=False

Range("B1").Select

Application.CutCopyMode = False

Selection.Copy

Range("B2").Select

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

:=False, Transpose:=False

End Sub

'-----------------------------------------------------------------

Private Sub Workbook_Open()

Sheets("I CALUIRE").Select

Range("B2:AG30").Select

Selection.Copy

ActiveWindow.ScrollWorkbookTabs Sheets:=-1

Sheets("I CALUIRE extraction").Select

Range("AA1").Select

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

:=False, Transpose:=False

End Sub

'-----------------------------------------------------------------

Private Sub Workbook_Open()

Sheets("IB CALUIRE").Select

Range("B2:AG30").Select

Selection.Copy

ActiveWindow.ScrollWorkbookTabs Sheets:=-1

Sheets("IB CALUIRE extraction").Select

Range("AA1").Select

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

:=False, Transpose:=False

End Sub

'-----------------------------------------------------------------

Private Sub Workbook_Open()

Sheets("K LIMOGES").Select

Range("B2:AG30").Select

Selection.Copy

ActiveWindow.ScrollWorkbookTabs Sheets:=-1

Sheets("K LIMOGES extraction").Select

Range("AA1").Select

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

:=False, Transpose:=False

End Sub

'-----------------------------------------------------------------

Private Sub Workbook_Open()

Sheets("PC MACON").Select

Range("B2:AG30").Select

Selection.Copy

ActiveWindow.ScrollWorkbookTabs Sheets:=-1

Sheets("PC MACON extraction").Select

Range("AA1").Select

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

:=False, Transpose:=False

End Sub

'-----------------------------------------------------------------

Private Sub Workbook_Open()

Sheets("I MELUN").Select

Range("B2:AG30").Select

Selection.Copy

ActiveWindow.ScrollWorkbookTabs Sheets:=-1

Sheets("I MELUN extraction").Select

Range("AA1").Select

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

:=False, Transpose:=False

End Sub

'-----------------------------------------------------------------

Private Sub Workbook_Open()

Sheets("I SAINT EMILION").Select

Range("B2:AG30").Select

Selection.Copy

ActiveWindow.ScrollWorkbookTabs Sheets:=-1

Sheets("I SAINT EMILION extraction").Select

Range("AA1").Select

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

:=False, Transpose:=False

End Sub

'-----------------------------------------------------------------

Private Sub Workbook_Open()

Sheets("IS BORDEAUX").Select

Range("B2:AG30").Select

Selection.Copy

ActiveWindow.ScrollWorkbookTabs Sheets:=-1

Sheets("IS BORDEAUX extraction").Select

Range("AA1").Select

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

:=False, Transpose:=False

End Sub

'-----------------------------------------------------------------

Private Sub Workbook_Open()

Sheets("IB LE MANS").Select

Range("B2:AG30").Select

Selection.Copy

ActiveWindow.ScrollWorkbookTabs Sheets:=-1

Sheets("IB LE MANS extraction").Select

Range("AA1").Select

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

:=False, Transpose:=False

End Sub

'-----------------------------------------------------------------

Private Sub Workbook_Open()

Sheets("IB TOURS").Select

Range("B2:AG30").Select

Selection.Copy

ActiveWindow.ScrollWorkbookTabs Sheets:=-1

Sheets("IB TOURS extraction").Select

Range("AA1").Select

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

:=False, Transpose:=False

End Sub

'-----------------------------------------------------------------

Private Sub Workbook_Open()

Sheets("IS BELFORT").Select

Range("B2:AG30").Select

Selection.Copy

ActiveWindow.ScrollWorkbookTabs Sheets:=-1

Sheets("IS BELFORT extraction").Select

Range("AA1").Select

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

:=False, Transpose:=False

End Sub

'-----------------------------------------------------------------

Private Sub Workbook_Open()

Sheets("K AIX").Select

Range("B2:AG30").Select

Selection.Copy

ActiveWindow.ScrollWorkbookTabs Sheets:=-1

Sheets("K AIX extraction").Select

Range("AA1").Select

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

:=False, Transpose:=False

End Sub

'-----------------------------------------------------------------

Private Sub Workbook_Open()

Sheets("C CHARTRES").Select

Range("B2:AG30").Select

Selection.Copy

ActiveWindow.ScrollWorkbookTabs Sheets:=-1

Sheets("C CHARTRES extraction").Select

Range("AA1").Select

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

:=False, Transpose:=False

End Sub

8montee-en-charge-2020-04-18-test.xlsm (540.38 Ko)

Le 18/04/2020 à 12:27

Bonjour,

Un essai ...

Private Sub Workbook_Open()
    Sheets("TOTAL").Range("B2").Copy
    Range("B3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Range("B1").Copy
    Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Sheets("I CALUIRE").Range("B2:AG30").Copy
    Sheets("I CALUIRE extraction").Range("AA1").PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Sheets("IB CALUIRE").Range("B2:AG30").Copy
    Sheets("IB CALUIRE extraction").Range("AA1").PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Sheets("K LIMOGES").Range("B2:AG30").Copy
    Sheets("K LIMOGES extraction").Range("AA1").PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Sheets("PC MACON").Range("B2:AG30").Copy
    Sheets("PC MACON extraction").Range("AA1").PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Sheets("I MELUN").Range("B2:AG30").Copy
    Sheets("I MELUN extraction").Range("AA1").PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Sheets("I SAINT EMILION").Range("B2:AG30").Copy
    Sheets("I SAINT EMILION extraction").Range("AA1").PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Sheets("IS BORDEAUX").Range("B2:AG30").Copy
    Sheets("IS BORDEAUX extraction").Range("AA1").PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Sheets("IB LE MANS").Range("B2:AG30").Copy
    Sheets("IB LE MANS extraction").Range("AA1").PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Sheets("IB TOURS").Range("B2:AG30").Copy
    Sheets("IB TOURS extraction").Range("AA1").PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Sheets("IS BELFORT").Range("B2:AG30").Copy
    Sheets("IS BELFORT extraction").Range("AA1").PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Sheets("K AIX").Range("B2:AG30").Copy
    Sheets("K AIX extraction").Range("AA1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False

    Sheets("C CHARTRES").Range("B2:AG30").Copy
    Sheets("C CHARTRES extraction").Range("AA1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
End Sub

ric

Le 18/04/2020 à 12:36

Bonjour Ric,

Ca fonctionne !!

Merci beaucoup

Fabrice

Le 18/04/2020 à 12:38

ric