Compile error: Procedure too long

Le 15/06/2015 à 11:07

Bonjour,

Je débute en VBA et je viens d'écrire un code ou ce message d'erreur apparait. Je savais bien que mon code n'était pas optimal, mais je ne pensais pas qu'il serait insupportable pour Excel

Je vous détaille le problème. Je suis en possession d'un fichier excel regroupant les informations de productions de trois équipes. Ce fichier est divisé en plusieurs feuilles - une feuille par jour.

Je voudrais effectué un copier/coller de certaines données dans un nouveau classeur excel afin ensuite de pourvoir les traiter plus aisément (tableau dynamique croisés etc etc..)

Voici un bout de mon code:

" ' Copie de données

' Jour 1

Workbooks("Mai2015.xlsx").Sheets("02052015").Range("C13:C55").Copy ' Projekt / Schicht A

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("A3").Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("02052015").Range("G13:G55").Copy ' Arbeitsgang / Schicht A

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("C3").Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("02052015").Range("M13:M55").Copy ' i.O / Schicht A

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("D3").Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("02052015").Range("N13:N55").Copy 'n.i.O / Schicht A

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("E3").Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("02052015").Range("C95:C137").Copy ' Projekt / Schicht B

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("A" & ActiveSheet.UsedRange.Rows.Count + 1).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("02052015").Range("G95:G137").Copy ' Arbeitsgang / Schicht B

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("C" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("02052015").Range("M95:M137").Copy ' i.O / Schicht B

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("D" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("02052015").Range("N95:N137").Copy 'n.i.O / Schicht B

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("E" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("02052015").Range("C177:C219").Copy ' Projekt / Schicht B

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("A" & ActiveSheet.UsedRange.Rows.Count + 1).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("02052015").Range("G177:G219").Copy ' Arbeitsgang / Schicht B

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("C" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("02052015").Range("M177:M219").Copy ' i.O / Schicht B

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("D" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("02052015").Range("N177:N219").Copy 'n.i.O / Schicht B

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("E" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

' Jour 2

Workbooks("Mai2015.xlsx").Sheets("04052015").Range("C13:C55").Copy ' Projekt / Schicht A

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("A" & ActiveSheet.UsedRange.Rows.Count + 1).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("04052015").Range("G13:G55").Copy ' Arbeitsgang / Schicht A

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("C" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("04052015").Range("M13:M55").Copy ' i.O / Schicht A

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("D" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("04052015").Range("N13:N55").Copy 'n.i.O / Schicht A

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("E" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("04052015").Range("C95:C137").Copy ' Projekt / Schicht B

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("A" & ActiveSheet.UsedRange.Rows.Count + 1).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("04052015").Range("G95:G137").Copy ' Arbeitsgang / Schicht B

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("C" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("04052015").Range("M95:M137").Copy ' i.O / Schicht B

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("D" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("04052015").Range("N95:N137").Copy 'n.i.O / Schicht B

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("E" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("04052015").Range("C177:C219").Copy ' Projekt / Schicht C

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("A" & ActiveSheet.UsedRange.Rows.Count + 1).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("04052015").Range("G177:G219").Copy ' Arbeitsgang / Schicht C

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("C" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("04052015").Range("M177:M219").Copy ' i.O / Schicht C

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("D" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("04052015").Range("N177:N219").Copy 'n.i.O / Schicht C

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("E" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

' Jour 3

Workbooks("Mai2015.xlsx").Sheets("05052015").Range("C13:C55").Copy ' Projekt / Schicht A

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("A" & ActiveSheet.UsedRange.Rows.Count + 1).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("05052015").Range("G13:G55").Copy ' Arbeitsgang / Schicht A

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("C" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("05052015").Range("M13:M55").Copy ' i.O / Schicht A

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("D" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("05052015").Range("N13:N55").Copy 'n.i.O / Schicht A

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("E" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("05052015").Range("C95:C137").Copy ' Projekt / Schicht B

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("A" & ActiveSheet.UsedRange.Rows.Count + 1).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("05052015").Range("G95:G137").Copy ' Arbeitsgang / Schicht B

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("C" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("05052015").Range("M95:M137").Copy ' i.O / Schicht B

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("D" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("05052015").Range("N95:N137").Copy 'n.i.O / Schicht B

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("E" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("05052015").Range("C177:C219").Copy ' Projekt / Schicht C

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("A" & ActiveSheet.UsedRange.Rows.Count + 1).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("05052015").Range("G177:G219").Copy ' Arbeitsgang / Schicht C

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("C" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("05052015").Range("M177:M219").Copy ' i.O / Schicht C

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("D" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste

Workbooks("Mai2015.xlsx").Sheets("05052015").Range("N177:N219").Copy 'n.i.O / Schicht C

Workbooks("Book1.xlsm").Activate

Workbooks("Book1.xlsm").Sheets("Sheet1").Range("E" & ActiveSheet.UsedRange.Rows.Count - 42).Select

Workbooks("Book1.xlsm").Sheets("Sheet1").Paste "

Je pense que mon erreur se situe dans le fait de coder "en dur" les jours (03052015, 04052015, 05052015). Existe-t-il une variante plus efficace ?

Merci beaucoup pour votre aide !

Le 15/06/2015 à 11:35

Bonjour,

une proposition, à tester

Sub Copie_de_données()

    Set wbi = Workbooks("Mai2015.xlsx")
    Set wbo = Workbooks("Book1.xlsm")
    Set wso = wbo.Sheets("sheet1")
    mois = "052015"
    jour = 0
    For i = 1 To 31
        dat = Format(i, "00") & mois
        On Error GoTo terreur
        Set wsi = wbi.Sheets(dat)
        On Error GoTo 0
        jour = jour + 1
        If jour = 1 Then
            wsi.Range("C13:C55").Copy wso.[A3]    ' Projekt / Schicht A
            wsi.Range("G13:G55").Copy wso.[C3]    ' Arbeitsgang / Schicht A
            wsi.Range("M13:M55").Copy wso.[D3]    ' i.O / Schicht A
            wsi.Range("N13:N55").Copy wso.[E3]    'n.i.O / Schicht A
        Else
            wsi.Range("C13:C55").Copy wso.Range("A" & ActiveSheet.UsedRange.Rows.Count + 1)    ' Projekt / Schicht A
            wsi.Range("G13:G55").Copy wso.Range("C" & ActiveSheet.UsedRange.Rows.Count - 42)    ' Arbeitsgang / Schicht A
            wsi.Range("M13:M55").Copy wso.Range("D" & ActiveSheet.UsedRange.Rows.Count - 42)    ' i.O / Schicht A
            wsi.Range("N13:N55").Copy wso.Range("E" & ActiveSheet.UsedRange.Rows.Count - 42)    'n.i.O / Schicht A
        End If

        wsi.Range("C95:C137").Copy wso.Range("A" & ActiveSheet.UsedRange.Rows.Count + 1)
        wsi.Range("G95:G137").Copy wso.Range("C" & ActiveSheet.UsedRange.Rows.Count - 42)    ' Arbeitsgang / Schicht B
        wsi.Range("M95:M137").Copy wso.Range("D" & ActiveSheet.UsedRange.Rows.Count - 42)    ' i.O / Schicht B
        wsi.Range("N95:N137").Copy wso.Range("E" & ActiveSheet.UsedRange.Rows.Count - 42)    'n.i.O / Schicht B
        wsi.Range("C177:C219").Copy wso.Range("A" & ActiveSheet.UsedRange.Rows.Count + 1)    ' Projekt / Schicht B
        wsi.Range("G177:G219").Copy wso.Range("C" & ActiveSheet.UsedRange.Rows.Count - 42)    ' Arbeitsgang / Schicht B
        wsi.Range("M177:M219").Copy wso.Range("D" & ActiveSheet.UsedRange.Rows.Count - 42)    ' i.O / Schicht B
        wsi.Range("N177:N219").Copy wso.Range("E" & ActiveSheet.UsedRange.Rows.Count - 42)    'n.i.O / Schicht B
ici:
        On Error GoTo 0
    Next i

    Exit Sub
terreur:
    Resume ici

End Sub
Le 15/06/2015 à 14:21

Il semble y avoir quelques ratés parfois avec le copié collé, je ne sais pas encore trop pourquoi, mais je vais tenter de résoudre cela.

Merci en tout cas pour la boucle, ca fonctionne super !

-Straw-

Rechercher des sujets similaires à "compile error procedure too long"