Recopie ligne formules par vba et optimisation

KIM

XLDnaute Accro
Bonjour le forum.
J'utilise souvent la technique ci-dessous:
Je definis mes formules sur une ligne
et je la recopie par macro dans la plage souhaitée.
Ex:
mes formules en G2:M2 ,
la macro 'Copie_ligneformules_dansplage'
recopie et calcule chaque formule dans la plage G5:M208

Mes données reelles font presque 60 000 lignes et 50 colonnes. et le temps d'exécution est tres tres long.
Je souhaite optimiser ce code et le rendre plus rapide en utilisant les tableaux. Cela me fera gagner beaucoup de temps.
Merci de votre aide
Amicalement
KIM

Sub Copie_ligneformules_dansplage()
Dim w_nfile As String
Dim MyPath As String
Dim Ws As Worksheet
Dim n_line As Long


Set Ws = Worksheets('DATA2')

With Application
.ScreenUpdating = False
' .ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

w_nfile = ActiveWorkbook.Name
MyPath = ActiveWorkbook.Path
Ws.Activate
Ws.Select
If Ws.AutoFilterMode Then
Selection.AutoFilter
End If

Windows(w_nfile).Activate
ActiveSheet.Calculate
n_line = Ws.Range('E2')

Range('G5:M' & n_line).Select
Selection.ClearContents


Range('G2:M2').Copy Destination:=Range('G5:G' & n_line)
ActiveSheet.Calculate
With Selection
.Copy
.Calculate
' .PasteSpecial Paste:=xlFormats
.PasteSpecial Paste:=xlValues
End With

With Application
.CutCopyMode = False
.DisplayAlerts = False
.ScreenUpdating = True
' .ScreenUpdating = False
.Calculation = xlCalculationManual
End With


MsgBox 'c'est fini'
End Sub [file name=prjKIMv1.zip size=35953]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prjKIMv1.zip[/file]
 

Pièces jointes

  • prjKIMv1.zip
    35.1 KB · Affichages: 104
B

bebere

Guest
bonjour Pierre-Jean
puisque tu parles de sauvegarde,dans le module2
une procédure(sauver une feuille d'un classeur) explications dans code
à bientôt [file name=prjKIMv3_20060602150052.zip size=40974]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prjKIMv3_20060602150052.zip[/file]
 

Pièces jointes

  • prjKIMv3_20060602150052.zip
    40 KB · Affichages: 22

ChTi160

XLDnaute Barbatruc
Salut KIM
bonjour le Team à bebere Lol
je vois que l'équipe méne à bien son travail
je n'ai pas encore regardé les derniers fichiers de pierrejean et bebere
et je me suis permis de modifier, les macros pour n'en faire qu'une et par la même Occasion j'ai utilisé d'autre posibilité qui permettent de supprimer les boucles et les copier coller
enfin à voir Lol
merci à la Dream Team de KIM Lol [file name=prjKIMv4_20060602160719.zip size=32860]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prjKIMv4_20060602160719.zip[/file]
 

Pièces jointes

  • prjKIMv4_20060602160719.zip
    32.1 KB · Affichages: 24

KIM

XLDnaute Accro
Re, Jeanmarie, Bebere et Pierrejean
@Pierrejean, idee interessante de metre les formules dans une feuille avec les cellules correspondantes,
et @Bebere de copier la feuille DATA2

Je vais tester sur des donnees reelles (20 000 lignes) vos macros et celle de Jeanmarie et j'adopterai celle qui est la plus rapide.
Merci
et Bon WE
KIM
 

KIM

XLDnaute Accro
Salut Jeanmarie,
je n'ai pas vu ton post,
effectivement le travail etait efficace et avec vous trois les idees ont ete bien mises en pratique. Je vais regarder ton fichier et te tiendrai au courant
Merci encore
KIM
 

pierrejean

XLDnaute Barbatruc
re

Bonsoir KIM ,JeanMarie ,bebere

la version avec copie de la Feuille DATA2 dans un nouveau dossier (pour simplifier enregistrement en C:/ modifiable a loisir)
nom du style DATA2_2_06_2006_17_30
dossier sans boutons ni formules ni macros (uniquement les valeurs dans une seule feuille) [file name=prj_KIMv1_20060602174454.zip size=38640]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prj_KIMv1_20060602174454.zip[/file]
 

Pièces jointes

  • prj_KIMv1_20060602174454.zip
    37.7 KB · Affichages: 25

KIM

XLDnaute Accro
Bonsoir à vous tous,
@Pierrejean,
exellente idee de copier DATA2 dans un nouveau dossier sans formules, ni macro ni boutons.
-Je viens de decouvrir cette ligne:
Workbooks.Add (xlWBATWorksheet)
Quel est le sens de xlWBATWorksheet?

-Si j'ai plusieurs feuilles DATA2, DATA3, ...
Suffirait-il de dupliquer la ligne
ActiveWorkbook.SaveAs Filename:='C:/DATA2_' & Format(Date, 'dd_mm_yyyy_' & Format(Time, 'hh_mm'))
pour recopier les feuilles DATAx dans un seul classeur resultat

- Y-a-t-il moyen d'avoir le nom du classeur courant dans le code vba via une fonction vba et non le coder en dur? cela evite le probleme du changement du nom du classeur par un utilisateur.

-Dans ces 2 lignes
Range('H5:O29').Copy
Range('H5:O29').PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

Est-il possible de parametrer dynamiquement la plage cible: Range('H5:O29')
H5 OK, 1ere cellule de la plage
parcontre la colonne 'O' est la derniere colonne de la ligne '4'
et '29' c'est la derniere ligne de la colonne G
Merci d'avance
KIM
 

KIM

XLDnaute Accro
Re,
@Jeanmarie,
je viens de regarder ta macro,
- Y-a-t-il une raison d'avoir dedoubler la ligne
.Range('H5:O' & .Range('G65536').End(xlUp).Row + 1).Value = _
.Range('H5:O' & .Range('G65536').End(xlUp).Row + 1).Value
- de meme est-il possible de rendre dynamique la colonne 'O' la plage. cette colonne depend des noms de DEP sans doublons de la colonne A qu'on copie dans la plage I4:xx4
Merci
KIM
 

pierrejean

XLDnaute Barbatruc
bonjour KIM JeanMarie bebere

version avec range dynamique

xlWBATWorksheet signifie classeur avec 1 seule feuille

pour le nom du fichier tout est envisageable (peux-tu preciser ta pensée)

enfin je n'ais pas prevu le cas de plusieurs feuilles DATA parceque je ne vois pas comment elles pourraient etre genérées
mais la encore on peut voir [file name=prj_KIMv1_20060603092826.zip size=39595]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prj_KIMv1_20060603092826.zip[/file]
 

Pièces jointes

  • prj_KIMv1_20060603092826.zip
    38.7 KB · Affichages: 26

KIM

XLDnaute Accro
Bonjour Pierrejean, Jeanmarie, Bebere et le forum
@Pierrejean,
Merci pour le range dynamique,
pour le nom du fichier:
1- Dans la ligne ci-dessous de la macro test
Workbooks('prj_KIMv1_3PJ.xls').Sheets('DATA2').Cells.Copy Destination:=ActiveWorkbook.Sheets('Feuil1').Range('A1')
Est-ce possible d'eviter de specifier en dur le nom du fichier prj_KIMv1_3PJ.xls etde mettre dans une variable le nom du fichier actif et ouvert et d'appeler cette variable dans la ligne de code ci-dessus. Cela permet de changer le nom du fichier sans modifier la ligne de code ci-dessus.
Merci d'avance
KIM
 

KIM

XLDnaute Accro
Re,
Suite ...
@Pierrejean,
2- Plusieurs feuilles DATA sont générées dans le fichier d'origine par des macros de type Tableau() sans la macro test()
et à la fin du calcul, recopier les differentes feuilles DATA dans un classeur sans formules, ni macro, ni boutons comme ta macro test actuelle.

Pour avir un fichier test on peut dans le meme fichier actuel prjKIM1 dupliquer la feuille DATA2 en DATA3 et DATA4
Merci
Bien amicalement
KIM
 

pierrejean

XLDnaute Barbatruc
bonsoir KIM bebere JeanMarie le forum

@ KIM

vois si j'ais bien compris tes souhaits [file name=prj_KIMv1_20060604185044.zip size=43408]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prj_KIMv1_20060604185044.zip[/file]
 

Pièces jointes

  • prj_KIMv1_20060604185044.zip
    42.4 KB · Affichages: 28

KIM

XLDnaute Accro
Bonjour Pierrejean, Jeanmarie, Bebere et le forum
J'espère que vous avez passé un agréable WE et peut-etre un WE prolongé.
@Pierrejean,
merci encore,
1- La recopie des plusiers feuilles DATA dans un classeur répond à ma demande, Je l'adapterai au cas où les feuilles dans le classeur initial ont des noms différents.
2- la macro test recopie la feuille complète.
Est-il possible de ne recopier que le résultat du calcul, c-à-d définir et nommer par vba une ou plusieurs plages plages de cellules
Ex G2:O2 nommée Titre et G4:O30 nommée Result (définie dynamiquement)
et recopier ces 2 plages Titre et Result
dans une feuille du nouveau classeur de meme nom à partir de la cellule B5 et avec 3 lignes entre les 2 plages?
Merci d'avance
KIM
 

Bebere

XLDnaute Barbatruc
Bonjour Kim,Pierrejean, Jeanmarie,le forum
une bonne réponse(et un bon point,Mam'zelle) si bien compris(lol)
à bientôt [file name=prj_KIMv1_20060605102423.zip size=44666]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prj_KIMv1_20060605102423.zip[/file]
 

Pièces jointes

  • prj_KIMv1_20060605102423.zip
    43.6 KB · Affichages: 23

KIM

XLDnaute Accro
Re,
@Pierrejean,
J'ai le meme fichier sans modif.
peux-tu m'envoyer ton dernier fichier modifié?

@Bebere,
effectivement la recopie est correcte pour la 1ere feuille DATA2,

Je vais essayer de mieux m'exprimer:
Pour eviter de copier toute la feuille ou de copier des plages de cellules comme dans ton exemple:
.Sheets('Data2').Range('G2:O2').Copy ...
.Sheets('Data2').Range('G4:O' & .Sheets('Data2').Range('G65536').End(xlUp).Row) ...

est-il possible de nommer ces plages à copier dans le fichier d'origine et dans la macro de copie utiliser les noms déjà definis. Cela permet de modifier , si necessaire, la definition des plages nommées sans modifier la macro de copie.

merci d'avance
KIM
 

Bebere

XLDnaute Barbatruc
rebonjour
cafouillé un bout de temps avec formules et calculs dans tableau,mais arrivé à un résultat
à bientôt [file name=prjKIMv46.zip size=44418]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prjKIMv46.zip[/file]
 

Pièces jointes

  • prjKIMv46.zip
    43.4 KB · Affichages: 31

Discussions similaires

Réponses
7
Affichages
292

Statistiques des forums

Discussions
311 733
Messages
2 082 019
Membres
101 872
dernier inscrit
Colin T