VBA filtre + copier/coller, rend erreurs
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 !!!
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
Salut
Compresse ton fichier avec winrar et heberge le ...
Et question ta macro fait pas stars wars quand elle s'éxecute
Merci pour ton aide seulement je suis au bureau et l informaticien n est pas la pour rentrer les codes administrateurs et installer winrar.
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
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....
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.
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