Problème de Copy Destination

Version imprimable

Bonjour, je suis débutant.
Mon code coince à la ligne copy

le fichier s'ouvre bien mais quand je veux copier les cellules A1 à... cela bloque.

Merci pour votre aide.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Sub TEST()
 
Dim wk, wk2 As Workbook
Dim ws, ws2 As Worksheet
Dim pth, prf1, prf2, prf3, suf, chemin, nom, Fichier As String
Dim dat, dat2 As Date
Dim derlign As Variant
 
derlign = Range("A1048576").End(xlUp).Row - 1
 
'        pth = "\\spvcertipost001\Expensys\Fedcom\2020\SPV\source_rapports_budget\source_rapport_krc\"
pth = "P:\VBA\BETA_KRC\file\"
suf = ".XLSX"
prf1 = "ZBUDRAP_"
prf2 = "ZBUDRAPCV_"
prf3 = "S_P99_ALL_ "
 
Set wk = ThisWorkbook
Set ws = wk.Worksheets("Feuil1")
 
dat = CDate("1900/01/01")
    nom = Dir(pth & prf1 & "*" & suf)
    Do While nom > ""
        nom = Trim(Replace(Replace(nom, prf1, ""), suf, ""))
        If IsNumeric(nom) And Len(nom) = 8 Then
            If IsDate(Format(nom, "####/##/##")) Then
                dat2 = CDate(Format(nom, "####/##/##"))
                If dat2 > dat Then Fichier = pth & prf1 & nom & suf: dat = dat2
            End If
        End If
        nom = Dir
        Debug.Print nom
    Loop
Debug.Print Fichier
 
 
Set wk2 = Workbooks.Open(Fichier, ReadOnly:=True)
wk2.Worksheets("Sheet1").Range("A2:A" & derlign).Copy
ws.Range("A2").Paste
wk2.Close True
Set wk2 = Nothing
 
End Sub

Il faut faire un collage spécial !
Code:

1 2 3 Range("A2").PasteSpecial Xl????? 'ou wk2.Worksheets("Feuil1").Range("A2:A" & derlign).Copy ws.Range("A2")
RangesSources.copy RangeDestinaton

Super le copy / paste fonctionne mais j'ai un autre problème
ma formule de somme.si.ens renvoie une erreur vba
pourquoi ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
Sub TEST()
 
derlign = Range("A1048576").End(xlUp).Row
cderlign = Cells(Rows.Count, 1).End(xlUp).Row + 1
 
 
'        pth = "\\spvcertipost001\Expensys\Fedcom\2020\SPV\source_rapports_budget\source_rapport_krc\"
pth = "P:\VBA\BETA_KRC\file\"
suf = ".XLSX"
prf1 = "ZBUDRAP_"
prf2 = "ZBUDRAPCV_"
prf3 = "S_P99_ALL_ "
 
Set wk = ThisWorkbook
Set ws = wk.Worksheets("Feuil1")
 
dat = CDate("1900/01/01")
    nom = Dir(pth & prf1 & "*" & suf)
    Do While nom > ""
        nom = Trim(Replace(Replace(nom, prf1, ""), suf, ""))
        If IsNumeric(nom) And Len(nom) = 8 Then
            If IsDate(Format(nom, "####/##/##")) Then
                dat2 = CDate(Format(nom, "####/##/##"))
                If dat2 > dat Then Fichier = pth & prf1 & nom & suf: dat = dat2
            End If
        End If
        nom = Dir
        Debug.Print nom
    Loop
Debug.Print Fichier
 
ws.Range("A2:A999").ClearContents
Set wk2 = Workbooks.Open(Fichier, ReadOnly:=True)
wk2.Worksheets("sheet1").Range("A2:A" & derlign - 1).Copy ws.Range("A2")
 
info = ws.Range("A" & Rows.Count).End(xlUp).Row
MsgBox info
ws.Range("A2:A" & info).RemoveDuplicates Columns:=1, Header:=xlNo
 
info = ws.Range("A" & Rows.Count).End(xlUp).Row
MsgBox info
 
For i = 2 To info
    somme = Application.WorksheetFunction.SumIfs(wk2.Worksheets("sheet1").Range(Cells(2, 2), Cells(cderlign - 1, 2)), wk2.Worksheets("sheet1").Range(Cells(1, 1), Cells(cderlign - 1, 1)), ws.Cells(i & 1))
    ws.Range("B" & i).Value = somme
Next i
 
 
 
'wk2.Close True
'Set wk2 = Nothing
 
End Sub

18/12/2020, 15h50
pijaku

Bonjour,

Cette syntaxe ws.Cells(i & 1) me semble curieuse (cf au bout de ta formule).
18/12/2020, 16h08
rossemma

sorry

Code:

somme = Application.WorksheetFunction.SumIfs(wk2.Worksheets("sheet1").Range(Cells(2, 2), Cells(cderlign - 1, 2)), wk2.Worksheets("sheet1").Range(Cells(1, 1), Cells(cderlign - 1, 1)), ws.Cells(i, 1))

Bonjour,
Curieux ces lignes de code avec la date. Pas le temps de faire un test pour l'instant mais cela me semble étrange
Code:

1 2 3 4 If IsDate(Format(nom, "####/##/##")) Then dat2 = CDate(Format(nom, "####/##/##")) If dat2 > dat Then Fichier = pth & prf1 & nom & suf: dat = dat2 End If
Citation:

j'ai un autre problème ma formule de somme.si.ens renvoie une erreur vba pourquoi ?

Comme l'a indiqué Franck, que je salue, le problème est que les objets Cells, ne sont pas reliés à leur parent qui est la feuille

Voici un exemple simple
Affichage de la somme des cellules de chaque ligne (de 2 à 4) des colonnes A & B de la feuille nommé CA. Soit pour la ligne 2, l'équivalent excel de =SOMME(CA!A2:B2)
Code:

1 2 3 4 5 6 7 8 9 10 11 Sub t() Dim sht As Worksheet Dim row As Long Set sht = ThisWorkbook.Worksheets("CA") For row = 2 To 4 With sht MsgBox "Somme de la ligne " & row & " est " & _ Format(Application.WorksheetFunction.Sum(.Range(.Cells(row, 1), .Cells(row, 2))), "#,###") End With Next End Sub
4

pourtant la feuille wk2 ici et ws en début de module est indiquée :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Set wk2 = Workbooks.Open(Fichier, ReadOnly:=True)
wk2.Worksheets("sheet1").Range("A2:A" & derlign - 1).Copy ws.Range("A2")
 
info = ws.Range("A" & Rows.Count).End(xlUp).Row
MsgBox info
ws.Range("A2:A" & info).RemoveDuplicates Columns:=1, Header:=xlNo
 
info = ws.Range("A" & Rows.Count).End(xlUp).Row
MsgBox info
 
For i = 2 To info
'    somme = Application.WorksheetFunction.SumIfs(wk2.Worksheets("sheet1").Range(Cells(2, 2), Cells(cderlign - 1, 2)), wk2.Worksheets("sheet1").Range(Cells(1, 1), Cells(cderlign - 1, 1)), ws.Cells(i, 1))
    ws.Range("B" & i).Value = "4"
Next i

18/12/2020, 16h43
Philippe Tulliez

Bonjour,

Citation:

pourtant la feuille wk2 ici et ws en début de module est indiquée :

Avez-vous bien lu ce que j'ai écrit ?
J'avais pourtant mis Cells en gras que je mets en rouge maintenant

Citation:

Comme l'a indiqué Franck, que je salue, le problème est que les objets Cells, ne sont pas reliés à leur parent qui est la feuille

Code partiel (sans le With Sht)

Code:

Sum(.Range(.Cells(row, 1), .Cells(row, 2)))

Vous voyez que Range, et Cells sont précédés d'un point qui les relie à ..... la feuille (Sht)
18/12/2020, 17h37
rossemma

j'avais mal compris, désolé

mais du coup comment faire, mes connaissances sont limitées à ce niveau.

J'essaie de faire une somme si où les montants sont dans le classeur WK2 et la donnée de référence dans WS
J'espère être clair

ex :
fichier wk2
Col A : AB
Col B : montant

fichier ws
col A : AB
col b = somme montant (ce que je veux faire)

Merci pour votre aide.
18/12/2020, 20h33
Philippe Tulliez

Bonjour,
Avant de traduire cela en VBA, avez-vous testé la fonction directement dans Excel ?
Dans l'affirmative, pouvez-vous publier la formule et ainsi nous pourrions, moi ou d'autres contributeurs, vous la traduire en VBA

Selon moi, c'est la meilleure façons de procéder pour vous expliquer comment y arriver et ce pour éviter un "bête" Copier/Coller qui ne vous apprendra rien et vous fera revenir ici dès que vous rencontrerez un problème.
18/12/2020, 21h01
rossemma

très bonne idée au moins je comprendrai le code vba
21/12/2020, 09h38
rossemma

Bonjour,

voici la formule :

Code:

=SOMME.SI.ENS([ZBUDRAP_20201215.XLSX]Sheet1!$B$2:$B$323;[ZBUDRAP_20201215.XLSX]Sheet1!$A$2:$A$323;$A2)

ZBUDRAP_20201215 ==> fichier wk2 dans mon code
A3 => fichier ws dans mon code

le nombre de ligne pouvant changer dans wk2 je souhaite utiliser derlign (voir mon code)
idem pour la boucle dans le fichier ws
for i = 2 to derlign je pense

merci pour votre aide
21/12/2020, 09h45
BENNASR

bonjour
malgré que c'est déconseillé... mais peux tu joindre un petit fichier test sans données confid et expliquer avec un exemple??
21/12/2020, 10h34
Philippe Tulliez
Bonjour,

Citation:

voici la formule :

Code:

=SOMME.SI.ENS([ZBUDRAP_20201215.XLSX]Sheet1!$B$2:$B$323;[ZBUDRAP_20201215.XLSX]Sheet1!$A$2:$A$323;$A2)

Je vous reviens avec la formule convertie en VBA avec les explications avant midi

Citation:

le nombre de ligne pouvant changer dans wk2 je souhaite utiliser derlign (voir mon code)

Je ne peux que vous conseiller de passer aux tableaux structurés. Après plus 10 ans d'existence, cela n'a plus de sens de travailler avec des tables classiques et de s'encombrer de lignes de code cherchant le nombre de lignes

A condition bien entendu que vous ayez une seule ligne pour les titres (la première) et qu'il n'y ait pas de cellules fusionnées, la conversion d'une table classique en un tableau structuré prend moins de 5 secondes mais que de temps gagné ensuite aussi bien pour le travail manuel avec Excel et pour la programmation

Je vous conseille la lecture de ces deux tutoriels de Pierre Fauconnier
1. Apprendre à utiliser les tableaux structurés Excel : création, manipulations et avantages
2. Excel: Les tables de données en VBA
21/12/2020, 16h40
Philippe Tulliez

3 pièce(s) jointe(s)
Bonjour,
Si j’ai bien compris votre demande, illustrée ci-dessous, un simple SOMME.SI aurait suffit

Pièce jointe 587100

Est-il impératif de passer par du VBA car plusieurs options s’offrent à vous ?
- Solution 1 : La formule tout simplement dans la colonne Montant du tableau de synthèse dont la table se nomme t_Syntheses (en noir sur l’illustration)
- Solution 2 : Passer par un tableau croisé dynamique
- Solution 3 : Si vous avez une version 2010 ou supérieure, utiliser PowerQuery, c’est réglé en quatre clics
- Solution 4 : La moins bonne le VBA
Je n'ai pas de problème de faire cela en VBA mais pour le faire proprement, il y a tout de même quelques lignes à écrire alors qu'avec la solution de Power Query, c'est moins de cinq clics et si c'est récurent, il suffit de faire un simple Refresh

Solution avec PowerQuery

Chargement et paramétrage du regroupement

Pièce jointe 587101

Finalisation avec tri sur colonne A

Pièce jointe 587102