VBA filtre + copier/coller, rend erreurs

Le 17/04/2014 à 14:12

Bonjour,

Après avoir éplucher les différentes discussions qui tournent autour de mon problème et n'avoir trouvé aucune solution je me décide enfin à créer un post; j'espère que vous pourrez m'aider. Voilà mon problème j'ai créé une macro pour copier/coller des éléments sur une autre feuille à partir d'une base de données filtrées; seulement quand je lance ma macro soit il ne me reporte pas toutes les valeurs des différentes colonnes que j'ai selectionnée et quand je supprime des éléments de ma base de données ils me les supprime pas forcement dans ma feuille macro.... D'après ce que j'ai lu. j'ai testé dans ma macro "selectionner, cellules visibles seulement " et aussi de cocher mon filtre + vides mais rien y fait !!! Bon en plus apparement je peux pas vous l envoyer car il est trop gros ! est ce que quelqu un aurait une idée ?

Voici ma macro si jamais ca peut vous aider (oui je sais elle fait peur ):

Sub Triweld()
'
' Triweld Macro
'
' Touche de raccourci du clavier: Ctrl+w
'
    Sheets("Follow-up").Select
    Application.ScreenUpdating = False
    ActiveSheet.Range("$A$8:$Q$1429").AutoFilter Field:=4, Criteria1:="=Weld", _
        Operator:=xlOr, Criteria2:="="
    Range("A1359").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Macro Weld").Select
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-21
    Sheets("Follow-up").Select
    ActiveWindow.ScrollRow = 1048540
    ActiveWindow.ScrollRow = 1046300
    ActiveWindow.ScrollRow = 766241
    ActiveWindow.ScrollRow = 495144
    ActiveWindow.ScrollRow = 302464
    ActiveWindow.ScrollRow = 170277
    ActiveWindow.ScrollRow = 123227
    ActiveWindow.ScrollRow = 62734
    ActiveWindow.ScrollRow = 15684
    ActiveWindow.ScrollRow = 11203
    ActiveWindow.ScrollRow = 1
    Range("B1359").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Macro Weld").Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Follow-up").Select
    ActiveWindow.ScrollRow = 1048540
    ActiveWindow.ScrollRow = 1041819
    ActiveWindow.ScrollRow = 869303
    ActiveWindow.ScrollRow = 732634
    ActiveWindow.ScrollRow = 551156
    ActiveWindow.ScrollRow = 396564
    ActiveWindow.ScrollRow = 284540
    ActiveWindow.ScrollRow = 179238
    ActiveWindow.ScrollRow = 132189
    ActiveWindow.ScrollRow = 71696
    ActiveWindow.ScrollRow = 22406
    ActiveWindow.ScrollRow = 4482
    ActiveWindow.ScrollRow = 1
    Range("F1359").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Macro Weld").Select
    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Follow-up").Select
    ActiveWindow.ScrollRow = 1048540
    ActiveWindow.ScrollRow = 1044059
    ActiveWindow.ScrollRow = 940998
    ActiveWindow.ScrollRow = 840176
    ActiveWindow.ScrollRow = 710229
    ActiveWindow.ScrollRow = 432411
    ActiveWindow.ScrollRow = 358476
    ActiveWindow.ScrollRow = 259895
    ActiveWindow.ScrollRow = 147872
    ActiveWindow.ScrollRow = 82898
    ActiveWindow.ScrollRow = 11203
    ActiveWindow.ScrollRow = 6722
    ActiveWindow.ScrollRow = 1
    Range("I1359").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Macro Weld").Select
    Range("D3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Follow-up").Select
    ActiveWindow.ScrollRow = 1048540
    ActiveWindow.ScrollRow = 1046300
    ActiveWindow.ScrollRow = 1028376
    ActiveWindow.ScrollRow = 795367
    ActiveWindow.ScrollRow = 504106
    ActiveWindow.ScrollRow = 306945
    ActiveWindow.ScrollRow = 219567
    ActiveWindow.ScrollRow = 125467
    ActiveWindow.ScrollRow = 29127
    ActiveWindow.ScrollRow = 1
    Range("K1359:L1359").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Macro Weld").Select
    Range("E3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Follow-up").Select
    ActiveWindow.ScrollRow = 1048540
    ActiveWindow.ScrollRow = 1039578
    ActiveWindow.ScrollRow = 911871
    ActiveWindow.ScrollRow = 672141
    ActiveWindow.ScrollRow = 407766
    ActiveWindow.ScrollRow = 264376
    ActiveWindow.ScrollRow = 150112
    ActiveWindow.ScrollRow = 62734
    ActiveWindow.ScrollRow = 53772
    ActiveWindow.ScrollRow = 31368
    ActiveWindow.ScrollRow = 17925
    ActiveWindow.ScrollRow = 1
    Range("N1359").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Macro Weld").Select
    Range("G3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Follow-up").Select
    ActiveWindow.ScrollRow = 1048540
    ActiveWindow.ScrollRow = 1046300
    ActiveWindow.ScrollRow = 1017173
    ActiveWindow.ScrollRow = 752798
    ActiveWindow.ScrollRow = 472740
    ActiveWindow.ScrollRow = 297983
    ActiveWindow.ScrollRow = 188200
    ActiveWindow.ScrollRow = 87379
    ActiveWindow.ScrollRow = 26887
    ActiveWindow.ScrollRow = 22406
    ActiveWindow.ScrollRow = 20165
    ActiveWindow.ScrollRow = 11203
    ActiveWindow.ScrollRow = 8963
    ActiveWindow.ScrollRow = 4482
    ActiveWindow.ScrollRow = 1
    Range("P1359").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Macro Weld").Select
    Range("H3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Follow-up").Select
    ActiveWindow.ScrollRow = 1048540
    ActiveWindow.ScrollRow = 1044059
    ActiveWindow.ScrollRow = 1032857
    ActiveWindow.ScrollRow = 853619
    ActiveWindow.ScrollRow = 557878
    ActiveWindow.ScrollRow = 315907
    ActiveWindow.ScrollRow = 156834
    ActiveWindow.ScrollRow = 62734
    ActiveWindow.ScrollRow = 47051
    ActiveWindow.ScrollRow = 2241
    ActiveWindow.ScrollRow = 1
    ActiveSheet.Range("$A$8:$Q$1429").AutoFilter Field:=4
    Application.ScreenUpdating = True
End Sub
Le 17/04/2014 à 14:37

Salut

Compresse ton fichier avec winrar et heberge le ...

Et question ta macro fait pas stars wars quand elle s'éxecute ?

Le 17/04/2014 à 15:14

Merci pour ton aide seulement je suis au bureau et l informaticien n est pas la pour rentrer les codes administrateurs et installer winrar. j ai essayé de le telecharger sur partage facile mais ca me met une erreur!

LOL effectivement c´est STAR WARS heureusement que j ai rajouté Application.ScreenUpdating = False et True!

Je pense pouvoir enregistrer le compresseur de chez moi et l envoyer ce soir. Merci en tout cas

Le 17/04/2014 à 15:41

Fichier trop lourd ? sur partage facile

Enfin bref je vais déjà voir essayer supprimer quelque ligne sur ton code et détailler ton code , après on verra....

Le 17/04/2014 à 15:50

il me met ça comment notification d'erreur sur partage facile :

"

Vous n'êtes pas connecté, les fichiers seront anonymes !

Selectionnez les fichiers... EnvoyerAnnuler

Follow-up of stock for critical material 01.xlsm Error TypeError: '0' a la valeur Null ou n'est pas un objet. "

Merci encore pour ton aide.

Le 17/04/2014 à 16:11

Re

Test ce code , j'ai supprimer le code inutile et mis des annotations pour que tu te retrouve dedans ,maintenant a partir de cela réflechis sur ton code et repose le probleme avec un maximun de détail (Et encore je suis pas sur de t'aider ...)

Sub Triweld()
'
' Triweld Macro
'
' Touche de raccourci du clavier: Ctrl+w
'
Sheets("Follow-up").Select
'Seecctionne feuille
Application.ScreenUpdating = False
'Cache les traitement
ActiveSheet.Range("$A$8:$Q$1429").AutoFilter Field:=4, Criteria1:="=Weld", _
Operator:=xlOr, Criteria2:="="
'Filtre weld
Range("A1359").Select
'selectionne case
Range(Selection, Selection.End(xlDown)).Select
'Arret a la premiere cellule non vide
Selection.Copy
'Copie
Sheets("Macro Weld").Select
'Selectionne feuille
Range("A3").Select
'Selectionne feuille
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Colle

Sheets("Follow-up").Select
Seelectionne feuille

Range("B1359").Select
'Selectionne cellule
Range(Selection, Selection.End(xlDown)).Select
'Selectionne premiere cellule non vide

Application.CutCopyMode = False
'vide le presse papier
Selection.Copy
copie
Sheets("Macro Weld").Select
'selectionne feuille
Range("B3").Select
Selectionne cellule
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'colle
Sheets("Follow-up").Select
'selectionne feuill

Range("F1359").Select
'selectionne Cellule
Range(Selection, Selection.End(xlDown)).Select
'Selectionne derniere cellule non vide
Application.CutCopyMode = False
'vide presse papier
Selection.Copy
'copie
Sheets("Macro Weld").Select
'selectionne feuille
Range("C3").Select
'selectionne cellule
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'colle
Sheets("Follow-up").Select
'selectionne feuille

Range("I1359").Select
'selectionne cellule
Range(Selection, Selection.End(xlDown)).Select
'derniere cellule non vide
Application.CutCopyMode = False
'vide presse papier
Selection.Copy
'copie
Sheets("Macro Weld").Select
'selectionne feuille
Range("D3").Select
'selectionne cellule
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'colle
Sheets("Follow-up").Select
'selectionne feuille

Range("K1359:L1359").Select
'selectionne plage
Range(Selection, Selection.End(xlDown)).Select
'selectionne derniere cellule non vide

Application.CutCopyMode = False
'vide presse papier
Selection.Copy
'copie
Sheets("Macro Weld").Select
'selectionne feuille
Range("E3").Select
'selectionne cellule
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'colle
Sheets("Follow-up").Select
'selectionne feuillle
Range("N1359").Select
seectionne cellule
Range(Selection, Selection.End(xlDown)).Select
'derniere cellulnon vide

Application.CutCopyMode = False
'vide presse papier
Selection.Copy
'copie
Sheets("Macro Weld").Select
'selectionne feuille
Range("G3").Select
'selectionne cellule
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Colle
Sheets("Follow-up").Select
'Selectionne feuille
Range("P1359").Select
'Selectionne cellule
Range(Selection, Selection.End(xlDown)).Select
'Derniere cellule non vide
Application.CutCopyMode = False
'Vide presse papier
Selection.Copy
'Copier
Sheets("Macro Weld").Select
'selectionne feuille
Range("H3").Select
'Selectionne h3
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Colle
Sheets("Follow-up").Select
'Selectionne feuille
ActiveSheet.Range("$A$8:$Q$1429").AutoFilter Field:=4
'filtre
Application.ScreenUpdating = True

End Sub
Rechercher des sujets similaires à "vba filtre copier coller rend erreurs"