XL 2019 Copier uniquement de valeurs (en ignorant les formules)

[ST4423]

Rebonjour à tous,

Second problème du jour pour moi : Je cherche un moyen de copier une plage de données en ne copiant que les valeurs (et tant qu'a faire si l'inverse est possible je suis preneur).

Je précise donc que je veux que la copie ignore les formules. Il ne s'agit pas de faire une copie avec collage spéciale des valeurs, auquel cas on écrase les formules par leurs valeurs.

En gros l'idée c'est que j'ai une colonne avec des formules dont plusieurs valeurs on été forcées, et je cherche à récupérer ces valeurs uniquement.

Merci d'avance.

 

[don_pets]

Alors voici par exemple une manière de procéder
su ton premier onglet en colonne g tu mets la formule

=SI(ESTFORMULE(B8)=VRAI;"";"Modifiée")

ainsi si c'est une formule on ne tope pas la ligne, ensuite tu mets en code une boucle du genre

ligne = 5
Do Until Cells(ligne, 1) = ""
    If Cells(ligne, 7).Value = "Modifiée" Then
        Cells(ligne, 2).Copy
        Sheets(2).Range("i65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If
ligne = ligne + 1
Loop

je te joins le fichier pour illustrer les propos

 

[don_pets]

là toujours dans le registre illustration , je copie les chiffres et non formules dans le deuxième onglet dans la colonne I

 

[Gégé-45550]

Voilà donc : Il s'agit d'une copie très grossière, mais dans l'idée ça serait ça pour la partie qui m'intéresse. Le fichier de base lui fait plus de 20000 lignes et a beaucoup plus de colonnes.

Maintenant si je souhaite copier la colonne "DI" en colonne B du fichier, je voudrais pouvoir copier uniquement les cellules qui sont en jaune, à savoir celles qui ont été forcées et qui ne sont plus des formules.

Bonjour,
Voici le code VBA pour identifier les cellules qui ne contiennent pas de formule (inutile d'écrire quoi que ce soit de plus dans la feuille "Liste régulation") :

Sub Copie()
Dim derlig&, i&, ws1 As Worksheet
    Set ws1 = ThisWorkbook.Worksheets("Liste régulation")
    derlig = Application.Max(Application.IfError(Application.Match(9 ^ 99, ws1.Columns(2)), 0), Application.IfError(Application.Match(String(255, "z"), ws1.Columns(2)), 0))
    For i = 5 To derlig
        If ws1.Cells(i, 2).Formula2 = True Then Debug.Print "Ligne " & i & " n'est pas une formule"
    Next i
End Sub

La question qui reste en suspens est la suivante :
Par quoi remplacer "Debug.Print "Ligne " & i & " n'est pas une formule"" ; en d'autres termes, où doivent être copiées ces valeurs et doivent-elles être copiées les unes en dessous des autres ou à leur place actuelle mais dans une colonne "sœur" qui contiendrait déjà des formules aux mêmes endroits que celles de la colonne "BI" ?
Cordialement,

 

[TooFatBoy]

Si j'ai bien compris, il y a un classeur source avec sa feuille, et un classeur cible avec la même feuille, et il faut copier les données saisies de la source vers la cible, même feuille, même endroit.

 

[Gégé-45550]

Si j'ai bien compris, il y a un classeur source avec sa feuille, et un classeur cible avec la même feuille, et il faut copier les fonnées saisies de la source vers la cible, même feuille, même endroit.

Si c'est bien ça, le code devient :

Sub Copie()
Dim derlig&, i&, ws1 As Worksheet, wsCible As Worksheet
    Set ws1 = ThisWorkbook.Worksheets("Liste régulation")
    Set wsCible = Workbook("MonFichierCibleOuvert").Worksheets("MaFeuilleDestination")
    derlig = Application.Max(Application.IfError(Application.Match(9 ^ 99, ws1.Columns(2)), 0), Application.IfError(Application.Match(String(255, "z"), ws1.Columns(2)), 0))
    For i = 5 To derlig
        If ws1.Cells(i, 2).Formula2 = True Then wsCible.Cells(i, 2) = ws1.Cells(i, 2)           'changer le 2 de wsCible.Cells(i, 2) si la colonne cible est à une position différente de 2
    Next i
End Sub

Cordialement,

 

[Cousinhub]

Bonjour,
Avec ma compréhension...
Le code :

Sub Copie_Const()
Dim T_Source As ListObject, T_Dest As ListObject
Dim Cel As Range
Dim Col As Long, Lig As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
Set T_Source = Range("Tableau2").ListObject 'nom du Tableau à adapter, ancien tableau
Set T_Dest = Range("Tableau1").ListObject 'nom du Tableau à adapter, nouveau tableau à modifier
Lig = T_Source.HeaderRowRange.Row: Col = T_Source.Range.Column - 1
For Each Cel In T_Source.DataBodyRange.SpecialCells(xlCellTypeConstants, 23)
    T_Dest.DataBodyRange(Cel.Row - Lig, Cel.Column - Col) = Cel.Value
Next Cel
Application.Calculation = xlCalculationAutomatic
End Sub

Le fichier test
Bonne soirée

 

[oguruma]

Mince, du coup j'avoue que je ne vois pas trop l'intérêt de ce code VBA étant donnée que la manipulation existe déjà en natif dans Excel via le collage spécial ?

bsr, en phase avec ta réflexion.... c'est qu'il manque qq part une fonction équivalente.
Confronté au problème pour devoir implémenter des RechercheX sur plusieurs milliers de lignes, je n'ai pas eu d'autres choix que coder sur la première cellule de la colonne la fonction RechercheX, de la propager (via un TS ça se fait automatiquement) puis récupérer la colonne et la remplacer par sa valeur. Donc pas d'autres choix que le VBA, d'autres te l'ont proposé mais j'ai ça aussi "sous le coude" via une classe. Si tu es preneur.... ça te permettra de découvrir la mise en oeuvre des classes en VBA

 

[ST4423]

Bonjour à tous,

Je découvre ce matin vos différents retours. Je vais donc essayer d'intégrer tout ça dans mon fichiers pour voir si cela fonctionne.

Si j'ai bien compris, il y a un classeur source avec sa feuille, et un classeur cible avec la même feuille, et il faut copier les données saisies de la source vers la cible, même feuille, même endroit.

C'est bien ça, les données à coller seront aux mêmes positions.

oguruma

bsr, en phase avec ta réflexion.... c'est qu'il manque qq part une fonction équivalente.
Confronté au problème pour devoir implémenter des RechercheX sur plusieurs milliers de lignes, je n'ai pas eu d'autres choix que coder sur la première cellule de la colonne la fonction RechercheX, de la propager (via un TS ça se fait automatiquement) puis récupérer la colonne et la remplacer par sa valeur. Donc pas d'autres choix que le VBA, d'autres te l'ont proposé mais j'ai ça aussi "sous le coude" via une classe. Si tu es preneur.... ça te permettra de découvrir la mise en oeuvre des classes en VBA

Je n'ai pas très bien compris de quoi il s’agissait. Je ne sais par exemple pas ce qu'est un "TS".
Mais par contre la version d'Excel que j'utilise n'intègre pas la fonction "RechercheX" (Excel 2019). Je ne pourrais donc à priori pas l'utiliser ?

En tout cas je vais étudier vos différentes solutions et vous fait un retour dès que possible. Merci à vous.

 

[ST4423]

J'ai regardé un peu les différentes solutions. Celle de Cousinhub me semble très bien fonctionnait pour le coup !
Par contre je n'ai pas réussi à appliquer vos méthodes Gégé-45550 et don_pets, désolé .

Concernant ta solution Cousinhub je me demandais pas contre : Comment fait tu pour changer la colonne à copier et coller ? J'aimerais par exemple pouvoir sélectionner plusieurs colonnes, en l'occurence les colonnes B à F de mon fichier en exemple.
Aussi je me demandais comment appliquer ce code dans le cas où je voudrais faire la manipulation entre deux fichiers ?

 

[ST4423]

Je reviens sur ce que j'ai écrit tout à l'heure :

Concernant ta solution Cousinhub je me demandais pas contre : Comment fait tu pour changer la colonne à copier et coller ? J'aimerais par exemple pouvoir sélectionner plusieurs colonnes, en l'occurence les colonnes B à F de mon fichier en exemple.

En faite le code permet de copier l'intégralité des cellules contenant des valeurs sur un nouveau tableau. Donc là dessus pour mon cas cela fonctionne très bien.
J'ai finalement créé ma copie sur une autre feuille pour faciliter la chose, et j'ai pu intégrer ce code à mon fichier final. Ca marche !

Cependant il y a un point qui m'embête un peu. Actuellement la macro ne copie pas les cellules dont la formules à été supprimée. Si le champ est vide, la cellule n'est pas copiée. Est-ce qu'il y aurait un moyen de résoudre ça ?

En gros si la formule revoie un champ vide je veux toujours l'ignorer mais si la formule renvoie une valeur que j'ai effacé manuellement je voudrais que la cellule soit copier comme telle.

 

[mutzik]

bonjour

avec une colonne cachée
=SI(ESTFORMULE(E8);0;SI(ESTNUM(E8);E8;""))
apres, on copie colle la colonne en valeurs

 

[ST4423]

Bonjour mutzik,

Ce n'est pas vraiment ce que je cherche.

Actuellement j'ai un code VBA pour copier uniquement les valeurs en texte (Qui exclu donc la copie des formules) dans un tableau vers un autre tableau. Seulement cette copie ignore aussi les cellules vide (sans formules), or je souhaiterais pouvoir copier les cellules vides quand ce n'est pas le résultat d'une formules.

Le code donne ça actuellement :

Sub Copie_Const()
Dim T_Source As ListObject, T_Dest As ListObject
Dim Cel As Range
Dim Col As Long, Lig As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
Set T_Source = Range("Tableau2").ListObject 'nom du Tableau à adapter, ancien tableau
Set T_Dest = Range("Tableau1").ListObject 'nom du Tableau à adapter, nouveau tableau à modifier
Lig = T_Source.HeaderRowRange.Row: Col = T_Source.Range.Column - 1
For Each Cel In T_Source.DataBodyRange.SpecialCells(xlCellTypeConstants, 23)
    T_Dest.DataBodyRange(Cel.Row - Lig, Cel.Column - Col) = Cel.Value
Next Cel
Application.Calculation = xlCalculationAutomatic
End Sub

Voici aussi le fichier que j'avais donné en exemple, avec la MàJ du code. Il n'est pas exhaustif par rapport à mon fichier par contre, car je ne peux pas diffuser le fichier original.

Je vu avec ta manip qu'il existé aussi une formule "ESTVIDE" mais je ne sais pas comment appliquer cette formule à un code VBA et surtout comment l'intégrer dans celui-ci.

 

[Gégé-45550]

Bonjour mutzik,

Ce n'est pas vraiment ce que je cherche.

Actuellement j'ai un code VBA pour copier uniquement les valeurs en texte (Qui exclu donc la copie des formules) dans un tableau vers un autre tableau. Seulement cette copie ignore aussi les cellules vide (sans formules), or je souhaiterais pouvoir copier les cellules vides quand ce n'est pas le résultat d'une formules.

Bonjour,
Remplacez la macro de @Cousinhub complétée comme suit :

Sub Copie_Const()
Dim T_Source As ListObject, T_Dest As ListObject
Dim Cel As Range
Dim Col As Long, Lig As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
Set T_Source = Range("Tableau2").ListObject 'nom du Tableau à adapter, ancien tableau
Set T_Dest = Range("Tableau1").ListObject 'nom du Tableau à adapter, nouveau tableau à modifier
Lig = T_Source.HeaderRowRange.Row: Col = T_Source.Range.Column - 1
For Each Cel In T_Source.DataBodyRange.SpecialCells(xlCellTypeConstants, 23)
    T_Dest.DataBodyRange(Cel.Row - Lig, Cel.Column - Col) = Cel.Value
Next Cel
For Each Cel In T_Source.DataBodyRange.Cells.SpecialCells(xlCellTypeBlanks)
    T_Dest.DataBodyRange(Cel.Row - Lig, Cel.Column - Col) = Cel.Value
Next Cel
Application.Calculation = xlCalculationAutomatic
End Sub

Cordialement,

 

[ST4423]

Bonjour,
Remplacez la macro de @Cousinhub complétée comme suit :

Sub Copie_Const()
Dim T_Source As ListObject, T_Dest As ListObject
Dim Cel As Range
Dim Col As Long, Lig As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
Set T_Source = Range("Tableau2").ListObject 'nom du Tableau à adapter, ancien tableau
Set T_Dest = Range("Tableau1").ListObject 'nom du Tableau à adapter, nouveau tableau à modifier
Lig = T_Source.HeaderRowRange.Row: Col = T_Source.Range.Column - 1
For Each Cel In T_Source.DataBodyRange.SpecialCells(xlCellTypeConstants, 23)
    T_Dest.DataBodyRange(Cel.Row - Lig, Cel.Column - Col) = Cel.Value
Next Cel
For Each Cel In T_Source.DataBodyRange.Cells.SpecialCells(xlCellTypeBlanks)
    T_Dest.DataBodyRange(Cel.Row - Lig, Cel.Column - Col) = Cel.Value
Next Cel
Application.Calculation = xlCalculationAutomatic
End Sub

Cordialement,

Bonjour @Gégé-45550.

Super ! Ça fonctionne.

J'ai pu l'intégrer sans problème dans mon fichier et tous semble en ordre quand je l’exécute.

Merci beaucoup pour votre aide à tous.