Script VBA pour copier des données d'un onglet à un autre dans Excel

Version imprimable

27/06/2024, 11h53
Socolatine99

1 pièce(s) jointe(s)

Script VBA pour copier des données d'un onglet à un autre dans Excel

Bonjour à tous,

Je suis tout nouveau sur le forum, et depuis cette année, je m'intéresse de plus près au langage VBA, étant comptable..

Je sollicite votre aide expérimentée sur un fichier qui est destiné à être importé en comptabilité. Ce sont des écritures bancaires.

L'idée est de copier les données en onglet "ECRITURES p1" à partir de la ligne 2 (colonne A à I) en ne prenant pas en compte les lignes "vides"

Et ensuite de coller la sélection dans l'onglet BQAMB CREDITMUTUEL sur la cellule D2

La deuxième étape est de faire la même chose pour les données en onglet "ECRITURES p2", et ensuite de coller la sélection sur la ligne en dessous de la dernière ligne collée venant du premier collage ECRITURES P1

Et en dernière étape, faire un tri sur la sélection à partir de D2:L2 jusqu'à la dernière ligne

Pouvez-vous m'apporter votre aide ?
27/06/2024, 13h03
Franc

Bonjour, je ne comprends pas l'intérêt de créer une macro vba pour un simple copier/coller ?
27/06/2024, 14h08
deedolith

Salut,

Avant de sortir la pseudo arme absolue VBA,
un explicatif des différent élements constitutifs de ton classeur s'impose.
Quelles sont les relations entre eux ?
Comment est supposé être utilisé le classeur (les règles de gestion) ?

Ce n'est que lorsque tu auras répondu à ces questions que l'on pourra décider si VBA est un outil adapté ou pas.
27/06/2024, 14h19
Socolatine99

Citation:

Envoyé par Franc

Bonjour, je ne comprends pas l'intérêt de créer une macro vba pour un simple copier/coller ?

Bonjour Franc,

Merci pour votre réponse, mon intérêt est d'obtenir un gain de temps optimisé car la maquette que j'ai jointe est utilisée pour une 30aine de sociétés dont je dois faire la comptabilité car manque de personnel.

De ce fait, gain de temps sur certaines tâches = permet de dégager du temps pour d'autres tâches.

Pour la première partie de ta question concernant la copie des données vers la colonne D de CREDITMUTUEL
Il vous reste l’élaboration du filtre pour exclure les lignes "vides" selon vos critères dans le code suivant il est configuré pour exclure les cellules avec valeur 0 dans la première colonne.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sub FilterAndCopy()
Dim Dat, DstSheet, rng, lastRow, N
Application.ScreenUpdating = False
 
Set DstSheet = Sheets("BQAMB CREDITMUTUEL")
DstSheet.Range("D2:L" & DstSheet.Rows.Count).ClearContents
 
For Each wPage In Array("ECRITURES p1", "ECRITURES p2") ' liste de page à traiter
  Set Dat = Sheets(wPage)
  lastRow = Dat.Cells(Dat.Rows.Count, 1).End(xlUp).Row
  Set rng = Dat.Range("A2:I" & lastRow)
  Set N = DstSheet.Cells(DstSheet.Rows.Count, 4).End(xlUp) ' dernier cellue vide CREDITMUTUEL  colonne D
  rng.AutoFilter Field:=1, _
          Criteria1:="<>0"
  rng.SpecialCells(xlCellTypeVisible).Copy
  N.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
  rng.AutoFilter
Next
Application.ScreenUpdating = True
End Sub

27/06/2024, 14h33
Socolatine99

Citation:

Envoyé par deedolith

Salut,

Avant de sortir la pseudo arme absolue VBA,
un explicatif des différent élements constitutifs de ton classeur s'impose.
Quelles sont les relations entre eux ?
Comment est supposé être utilisé le classeur (les règles de gestion) ?

Ce n'est que lorsque tu auras répondu à ces questions que l'on pourra décider si VBA est un outil adapté ou pas.

Bonjour Deedolith,

Je vais tenter d'expliquer le fonctionnement de cette maquette, qui permet de modéliser les relevés bancaires en écritures comptables, générées grâce aux différentes formules et interconnexions entre chaque onglet, et que j'importe donc dans mon logiciel comptable :

1) Chaque mois, j'édite les relevés bancaires en format excel --> ces relevés bancaires je les copie colle dans l'onglet "RELEVE BANCAIRE", j'obtiens directement les comptes correspondants aux écritures ainsi que le nom des fournisseurs concernés par ces écritures grâce à une base de données qui se trouve dans l'onglet "BASE"

2) Une fois que les écritures sont collées dans l'onglet "RELEVE BANCAIRE" et également après quelques corrections dans ce même onglet, sont générées grâce à des formules, une partie des écritures comptables dans les onglets "ECRITURES P1" et "ECRITURES P2"

3)Pour construire l'écriture comptable, je dois copier coller les écritures générées de l'onglet "ECRITURES P1" dans l'onglet "BQAMB CREDITMUTUEL", je répète l'opération pour les écritures générées de l'onglet "ECRITURES P2" à coller dans l'onglet "BQAMB CREDIT MUTUEL" sauf que ces écritures ci doivent être collées après la dernière ligne des écritures générées de l'onglet "ECRITURES P1"

4) Pour finir, lorsque les écritures générées des onglets "ECRITURES P1" et "ECRITURES P2" sont collées, je dois effectuer un tri qui permet de bien modéliser l'écriture comptable, avec un solde débit et crédit égaux, et l'écriture sera importée en comptabilité.

J'espère avoir été assez limpide dans mes explications, si vous avez d'autres question, n'hésitez pas !

Merci !

Citation:
Envoyé par Volid
Pour la première partie de ta question concernant la copie des données vers la colonne D de CREDITMUTUEL
Il vous reste l’élaboration du filtre pour exclure les lignes "vides" selon vos critères dans le code suivant il est configuré pour exclure les cellules avec valeur 0 dans la première colonne.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Sub FilterAndCopy() Dim Dat, DstSheet, rng, lastRow, N Application.ScreenUpdating = False Set DstSheet = Sheets("BQAMB CREDITMUTUEL") DstSheet.Range("D2:L" & DstSheet.Rows.Count).ClearContents For Each wPage In Array("ECRITURES p1", "ECRITURES p2") ' liste de page à traiter Set Dat = Sheets(wPage) lastRow = Dat.Cells(Dat.Rows.Count, 1).End(xlUp).Row Set rng = Dat.Range("A2:I" & lastRow) Set N = DstSheet.Cells(DstSheet.Rows.Count, 4).End(xlUp) ' dernier cellue vide CREDITMUTUEL colonne D rng.AutoFilter Field:=1, _ Criteria1:="<>0" rng.SpecialCells(xlCellTypeVisible).Copy N.Offset(1, 0).PasteSpecial Paste:=xlPasteValues rng.AutoFilter Next Application.ScreenUpdating = True End Sub
Bonjour Volid,

Merci beaucoup pour votre contribution, ce premier élément de réponse fonctionne très bien !

Comment procéder pour que les écritures de l'onglet "ECRITURES P2" se collent à la suite du premier collage issu des écritures de l'onglet "ECRITURES P1"

27/06/2024, 15h43
deedolith

Au vue de ta description, VBA est strictement inutile.
Des tableaux, des formules et Power Query feront le job.

Pas trop le temps de répondre en détail de suite.
27/06/2024, 18h02
Invité
Citation:

Comment procéder pour que les écritures de l'onglet "ECRITURES P2" se collent à la suite du premier collage issu des écritures de l'onglet "ECRITURES P1"

Le code proposé fait les deux opérations: après le premier collage on cherche la dernière ligne vide dans la colonne D pour y placer les données de la seconde opération d'importation.
Code:

1 2 3 Set N = DstSheet.Cells(DstSheet.Rows.Count, 4).End(xlUp) ' dernier cellue vide CREDITMUTUEL colonne D .. N.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Le paramètre correct du filtre devrait être (field := 2) la colonne 2 (date) sur laquelle le filtrage est effectué
28/06/2024, 00h53
deedolith

Il va falloir être plus précis, les règles de gestions que tu as donné sont floues ou incomplètes.

Feuille "RELEVE BANCAIRE":
Colonne A à E, C'est quoi ? A quoi ca sert ?
A mon avis, ça ressemble à une synthèse (des sommes ?), donc rien à faire dans cette feuille, à séparer.

Dans quelles colonnes colles tu tes données ? F, G, H, I, L, M, N, O ?

"Quelques corrections", Lesquelles ?

"une partie des écritures comptables dans les onglets "ECRITURES P1" et "ECRITURES P2", Selon quels critères ?
28/06/2024, 15h02
Socolatine99
Citation:
Envoyé par Volid

Le code proposé fait les deux opérations: après le premier collage on cherche la dernière ligne vide dans la colonne D pour y placer les données de la seconde opération d'importation.

Code:

1 2 3 Set N = DstSheet.Cells(DstSheet.Rows.Count, 4).End(xlUp) ' dernier cellue vide CREDITMUTUEL colonne D .. N.Offset(1, 0).PasteSpecial Paste:=xlPasteValues

Le paramètre correct du filtre devrait être (field := 2) la colonne 2 (date) sur laquelle le filtrage est effectué
Bonjour Volid !

J'ai modifié (field := 1) en (field :=2) et c'est exactement le résultat escompté, merci beaucoup !!!

Et pour la dernière étape, est-il possible de rajouter au script une commande pour faire un tri, une fois les deux "écritures P1" et "écritures P2" importées dans l'onglet "BQ AMB CREDITMUTUEL", sur la colonne F / valeurs / du plus petit au plus grand, en ayant sélectionné toutes les lignes renseignées (exceptées les "lignes vides") à partir de la plage D2:L2 jusquà la dernière ligne renseignée ?
28/06/2024, 15h11
Socolatine99

Citation:

Envoyé par deedolith

Il va falloir être plus précis, les règles de gestions que tu as donné sont floues ou incomplètes.

Feuille "RELEVE BANCAIRE":
Colonne A à E, C'est quoi ? A quoi ca sert ?
A mon avis, ça ressemble à une synthèse (des sommes ?), donc rien à faire dans cette feuille, à séparer.

Dans quelles colonnes colles tu tes données ? F, G, H, I, L, M, N, O ?

"Quelques corrections", Lesquelles ?

"une partie des écritures comptables dans les onglets "ECRITURES P1" et "ECRITURES P2", Selon quels critères ?

Bonjour Deedolith,

Merci pour votre sollicitude, Volid a répondu à mes attentes !

-Les colonnes A à E sont le reflet des relevés bancaires générées en fichier excel que je dois importer en comptabilité. Les colonnes de F à O servent aussi à modéliser l'écriture notamment au niveau des comptes correspondant aux libellés (colonne J et K)

-Les corrections que je dois apporter concernent les comptes qui peuvent être parfois mal modélisés, grâce à l'onglet BASE où sont répertoriés tous les comptes comptables utilisés dans ma comptabilité.
Exemple : J'ai une opération libellée "PASSION FROID", dans ma base de données j'ai un compte fournisseur 401PASSIONFROID et un compte 4421000 PAS (Prelevement à la source), il arrive souvent que le libellé PASSIONFROID soit modélisé en 44210000 PAS alors que ca devrait être 401PASSIONFROID, c'est là que j'apporte ma correction dans l'onglet BASE, pour que ca se modélise correctement.

-Quand je dis une partie des écritures comptables dans les onglets ECRITURES P1 et ECRITURES P2, ce sont les lignes qui sont renseignées avec une date un montant (débit et crédit) et un libellé.
28/06/2024, 19h13
Invité
Citation:

Et pour la dernière étape, est-il possible de rajouter au script une commande pour faire un tri, une fois les deux "écritures P1" et "écritures P2" importées dans l'onglet "BQ AMB CREDITMUTUEL", sur la colonne F / valeurs / du plus petit au plus grand, en ayant sélectionné toutes les lignes renseignées (exceptées les "lignes vides") à partir de la plage D2:L2 jusquà la dernière ligne renseignée ?

Ajouter ce code pour l’opération de tri juste avant la ligne Application.ScreenUpdating = True à la fin de la fonction
Code:

1 2 3 4 5 lastRow = DstSheet.Cells(DstSheet.Rows.count, 4).End(xlUp).Row With DstSheet.range("D2:L" & lastRow) .Sort Key1:=DstSheet.range("F2") End With DstSheet.range("D2").Select
29/06/2024, 20h25
deedolith

1 pièce(s) jointe(s)

Si j'ai à peut près compris, ca devrait donner quelque chose comme suit:
Pièce jointe 656626

Tout est fait via Power Query.
01/07/2024, 10h58
Socolatine99
Citation:
Envoyé par Volid

Ajouter ce code pour l’opération de tri juste avant la ligne Application.ScreenUpdating = True à la fin de la fonction

Code:

1 2 3 4 5 lastRow = DstSheet.Cells(DstSheet.Rows.count, 4).End(xlUp).Row With DstSheet.range("D2:L" & lastRow) .Sort Key1:=DstSheet.range("F2") End With DstSheet.range("D2").Select
Bonjour Volid,

C'est parfaitement parfait, un grand grand merci à vous pour votre temps et votre aide !!
01/07/2024, 11h00
Socolatine99

Citation:

Envoyé par deedolith

Si j'ai à peut près compris, ca devrait donner quelque chose comme suit:
Pièce jointe 656626

Tout est fait via Power Query.

Bonjour Deedolith,

Merci encore pour votre aide, les lignes de code de Volid correspondent et répondent parfaitement à mes attentes.