Sujet Précédent Sujet Suivant

VBA Excel - Copier Coller ne fonctionne pas

RodolpheG Messages postés 13 Date d'inscription vendredi 25 novembre 2022 Statut Membre Dernière intervention 20 décembre 2023 - 25 juil. 2023 à 14:33

Bonjour

Ma macro ci dessous actualise la feuille "Données" mais ne copie colle pas la cette feuille actualisée, elle copie colle l'ancienne version, il faut que je la lance une seconde fois pour le copie colle fonctionne correctement. Chose surprenant quand je lance ma macro au pas à pas, en revanche elle fonctionne.

J'ai essayé de voir du coté du presse papier avec ce code mais je n'obtiens pas de meilleurs résultats

J'ai également essayé de voir avec Application.CutCopyMode = False ou Application.CutCopyMode = XlCopy mais sans succès

Merci de votre aide 

Cdt

RG

Public Declare Function OuvrirPressEPapiers Lib "user32" Alias "OpenClipboard" (ByVal hwnd As Long) As Long
Public Declare Function ViderPressePapiers Lib "user32" Alias "EmptyClipboard" () As Long
Public Declare Function FermerPressePapiers Lib "user32" Alias "CloseClipboard" () As Long
 
Public Function NettoyerPressePapiers()
  OuvrirPressEPapiers (0&)
  ViderPressePapiers
  FermerPressePapiers
End Function

Sub TestNettoyerPressePapiers()
  Call NettoyerPressePapiers
End Sub

Sub Macro1()

    Dim AnneeValue_XL As Integer
    Dim MoisValue_XL As Integer
    
    Sheets("Données").Visible = True
'    Sheets("DonnéesCopie").Visible = True
'    Sheets("DonnéesCopie").Delete
'
    Application.Calculate
    Application.CutCopyMode = False
    
    Sheets("Données").Select
    Range("D10").Select
    ActiveWorkbook.RefreshAll

    
    Sheets(Array("Janvier", "Fevrier", "Mars", "Avril", "Mai", "Juin", "Juillet", "Aout", "Septembre", "Octobre", "Novembre", "Decembre")).Select
    Range("A100:P100").Select
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    Range("A5:P100").Select
    Range("A100").Activate
    ActiveSheet.Paste
    'Application.CutCopyMode = False

    Sheets("Données").Select
 
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row


    For x = 2 To FinalRow

   
            TypeValue = Cells(x, 2).Value
            AnneeValue = Cells(x, 7).Value
            MoisValue = Cells(x, 8).Value
            MoisValue_XL = Cells(1, 16).Value
            AnneeValue_XL = Cells(1, 15).Value

                If TypeValue = "Entrées CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Janvier").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Entrées CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then
            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Janvier").Select
            NextRow = Cells(Rows.Count, 5).End(xlUp).Row + 1
            Cells(NextRow, 5).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Janvier").Select
            NextRow = Cells(Rows.Count, 9).End(xlUp).Row + 1
            Cells(NextRow, 9).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Janvier").Select
            NextRow = Cells(Rows.Count, 13).End(xlUp).Row + 1
            Cells(NextRow, 13).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                End If

            MoisValue_XL = Cells(2, 16).Value

                If TypeValue = "Entrées CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Fevrier").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Entrées CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then
            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Fevrier").Select
            NextRow = Cells(Rows.Count, 5).End(xlUp).Row + 1
            Cells(NextRow, 5).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Fevrier").Select
            NextRow = Cells(Rows.Count, 9).End(xlUp).Row + 1
            Cells(NextRow, 9).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Fevrier").Select
            NextRow = Cells(Rows.Count, 13).End(xlUp).Row + 1
            Cells(NextRow, 13).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                End If

            MoisValue_XL = Cells(3, 16).Value

                If TypeValue = "Entrées CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Mars").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Entrées CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then
            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Mars").Select
            NextRow = Cells(Rows.Count, 5).End(xlUp).Row + 1
            Cells(NextRow, 5).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Mars").Select
            NextRow = Cells(Rows.Count, 9).End(xlUp).Row + 1
            Cells(NextRow, 9).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Mars").Select
            NextRow = Cells(Rows.Count, 13).End(xlUp).Row + 1
            Cells(NextRow, 13).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                End If

            MoisValue_XL = Cells(4, 16).Value

                If TypeValue = "Entrées CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Avril").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Entrées CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then
            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Avril").Select
            NextRow = Cells(Rows.Count, 5).End(xlUp).Row + 1
            Cells(NextRow, 5).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Avril").Select
            NextRow = Cells(Rows.Count, 9).End(xlUp).Row + 1
            Cells(NextRow, 9).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Avril").Select
            NextRow = Cells(Rows.Count, 13).End(xlUp).Row + 1
            Cells(NextRow, 13).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                End If

            MoisValue_XL = Cells(5, 16).Value

                If TypeValue = "Entrées CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Mai").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Entrées CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then
            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Mai").Select
            NextRow = Cells(Rows.Count, 5).End(xlUp).Row + 1
            Cells(NextRow, 5).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Mai").Select
            NextRow = Cells(Rows.Count, 9).End(xlUp).Row + 1
            Cells(NextRow, 9).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Mai").Select
            NextRow = Cells(Rows.Count, 13).End(xlUp).Row + 1
            Cells(NextRow, 13).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                End If

            MoisValue_XL = Cells(6, 16).Value

                If TypeValue = "Entrées CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Juin").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Entrées CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then
            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Juin").Select
            NextRow = Cells(Rows.Count, 5).End(xlUp).Row + 1
            Cells(NextRow, 5).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Juin").Select
            NextRow = Cells(Rows.Count, 9).End(xlUp).Row + 1
            Cells(NextRow, 9).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Juin").Select
            NextRow = Cells(Rows.Count, 13).End(xlUp).Row + 1
            Cells(NextRow, 13).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                End If

            MoisValue_XL = Cells(7, 16).Value

                If TypeValue = "Entrées CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Juillet").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Entrées CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then
            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Juillet").Select
            NextRow = Cells(Rows.Count, 5).End(xlUp).Row + 1
            Cells(NextRow, 5).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Juillet").Select
            NextRow = Cells(Rows.Count, 9).End(xlUp).Row + 1
            Cells(NextRow, 9).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Juillet").Select
            NextRow = Cells(Rows.Count, 13).End(xlUp).Row + 1
            Cells(NextRow, 13).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                End If

             MoisValue_XL = Cells(8, 16).Value

                If TypeValue = "Entrées CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Aout").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Entrées CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then
            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Aout").Select
            NextRow = Cells(Rows.Count, 5).End(xlUp).Row + 1
            Cells(NextRow, 5).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Aout").Select
            NextRow = Cells(Rows.Count, 9).End(xlUp).Row + 1
            Cells(NextRow, 9).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Aout").Select
            NextRow = Cells(Rows.Count, 13).End(xlUp).Row + 1
            Cells(NextRow, 13).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                End If

            MoisValue_XL = Cells(9, 16).Value

                If TypeValue = "Entrées CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Septembre").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Entrées CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then
            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Septembre").Select
            NextRow = Cells(Rows.Count, 5).End(xlUp).Row + 1
            Cells(NextRow, 5).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Septembre").Select
            NextRow = Cells(Rows.Count, 9).End(xlUp).Row + 1
            Cells(NextRow, 9).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Septembre").Select
            NextRow = Cells(Rows.Count, 13).End(xlUp).Row + 1
            Cells(NextRow, 13).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                End If

            MoisValue_XL = Cells(10, 16).Value

                If TypeValue = "Entrées CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Octobre").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Entrées CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then
            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Octobre").Select
            NextRow = Cells(Rows.Count, 5).End(xlUp).Row + 1
            Cells(NextRow, 5).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Octobre").Select
            NextRow = Cells(Rows.Count, 9).End(xlUp).Row + 1
            Cells(NextRow, 9).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Octobre").Select
            NextRow = Cells(Rows.Count, 13).End(xlUp).Row + 1
            Cells(NextRow, 13).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                End If

            MoisValue_XL = Cells(11, 16).Value

                If TypeValue = "Entrées CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Novembre").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Entrées CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then
            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Novembre").Select
            NextRow = Cells(Rows.Count, 5).End(xlUp).Row + 1
            Cells(NextRow, 5).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Novembre").Select
            NextRow = Cells(Rows.Count, 9).End(xlUp).Row + 1
            Cells(NextRow, 9).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Novembre").Select
            NextRow = Cells(Rows.Count, 13).End(xlUp).Row + 1
            Cells(NextRow, 13).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                End If

            MoisValue_XL = Cells(12, 16).Value

                If TypeValue = "Entrées CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Decembre").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Entrées CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then
            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Decembre").Select
            NextRow = Cells(Rows.Count, 5).End(xlUp).Row + 1
            Cells(NextRow, 5).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDD" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Decembre").Select
            NextRow = Cells(Rows.Count, 9).End(xlUp).Row + 1
            Cells(NextRow, 9).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                ElseIf TypeValue = "Sorties CDI" And MoisValue = MoisValue_XL And AnneeValue = AnneeValue_XL Then

            Cells(x, 3).Resize(1, 4).Copy
            Sheets("Decembre").Select
            NextRow = Cells(Rows.Count, 13).End(xlUp).Row + 1
            Cells(NextRow, 13).Select
            ActiveSheet.Paste
            Sheets("Données").Select

                End If

    Next x
    
Application.Calculate
Sheets("Janvier").Select
Range("A3").Select

Sheets("Données").Visible = False

MsgBox "Fin" & Chr(10) & "Si vous souhaitez enregistrer ce document; pensez à enregistrer sous : ... et le nommer differemment !!", vbInformation, "Message"

End Sub
A voir également:

Discussions similaires

Dessiner avec son clavier et avec des lettres
JSS -
Polodu33 -

78 réponses
recopier une formule sans décalage
aziz_alg80 -
aziz_alg80 -

2 réponses
Comment faire le symbole coeur vide/coeur blanc
Paria -
2008G.C -

60 réponses