Dim tablo tablo = Workbooks("toto").Sheets("Feuil1").Range("A1:" & Workbooks("toto").Sheets("feuil1").Cells.SpecialCells(xlCellTypeLastCell).Address) For i = 1 To UBound(tablo, 2) Select Case Trim(tablo(1, i)) Case "Learner Area", "Learner Sub-Area", "Learner Country", "Learner Company", _ "Type", "Job", "Certification Level", "Release", "Reference", "Title", "Start Date" tablo(1, i) = Replace(tablo(1, i), " ", "_") tablo(1, i) = Replace(tablo(1, i), ":", "") tablo(1, i) = Replace(tablo(1, i), "/", "_") Case "Classroom Duration (Trainee Days)" tablo(1, i) = "Classroom_Duration_Days" Case "E-learning Duration (Hours)" tablo(1, i) = "Elearning_Duration_Hours" Case "Internal/External" tablo(1, i) = "Int_Ext" Case Else tablo(1, i) = "" End Select Next Workbooks("titi").Sheets("Feuil2").Range("A1:" & Workbooks("toto").Sheets("Feuil1").Cells.SpecialCells(xlCellTypeLastCell).Address) = tablo Workbooks("titi").Sheets("Feuil2").Rows(1).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questiondonc c bien ce que je pensais dans ton exemple ca marchait pas il faut bien le workbook
For i = 1 To nbColonnes If Cells(1, i) "Learner Area" Or Cells(1, i) "Learner Sub-Area" Or Cells(1, i) = "Learner Country" Or Cells(1, i) = "Learner Country" Or Cells(1, i) = "Learner Company" Or Cells(1, i) = "Type" Or Cells(1, i) = "Job" Or Cells(1, i) = "Certification Level" Or Cells(1, i) = "Release" Or Cells(1, i) = "Reference" Or Cells(1, i) = "Title" Or Cells(1, i) = "Start Date" Or Cells(1, i) = "Classroom Duration (Trainee Days)" Or Cells(1, i) = "E-learning Duration (Hours)" Or Cells(1, i) = "Internal/External" Then nbColonnesAfter = nbColonnesAfter + 1 If Cells(1, i) = "Classroom Duration (Trainee Days)" Then SaveColonnes(1, nbColonnesAfter) = "Classroom_Duration_Days" changement = True End If If Cells(1, i) = "E-learning Duration (Hours)" Then SaveColonnes(1, nbColonnesAfter) = "Elearning_Duration_Hours" changement = True End If If Cells(1, i) = "Internal/External" Then SaveColonnes(1, nbColonnesAfter) = "Int_Ext" changement = True End If If changement = False Then Champs = Cells(1, i).Text Champs = Replace(Champs, " ", "_") Champs = Replace(Champs, ":", "") Champs = Replace(Champs, "/", "_") SaveColonnes(1, nbColonnesAfter) = Champs End If changement = False For j = 2 To nbLignes SaveColonnes(j, nbColonnesAfter) = Cells(j, i) Next j End If Next i For i = 1 To nbLignes Next i Dim wb As Workbook Dim ws As Worksheet Set wb = Workbooks.Open(file) Set ws = wb.Worksheets(1) For i = 1 To nbColonnesAfter For j = 1 To nbLignes Workbooks(Parametres.FileName.Text & extension).Sheets(Parametres.FileName.Text).Range(AlphabetColonne(i) & j).Value = SaveColonnes(j, i) Next j Next i
If Cells(1, i) = "Learner Area" ... then
If Cells(1, i) = "Classroom Duration (Trainee Days)" Then
'...etc...
ma feuille source est variable donc le nombre de colonens et de lignes aussi
Juxtaposées ou discontinues ... heu .... bah.... heu ....
bah je comprend pas la différence désolé ....
ces colonnes à copier sont-elles juxtaposées ou discontinues ?
Il y a 14 colonnes a récupérer
elles suivent toutes de A jusqu à AA
For i = 1 To nbColonnes Select Case Cells(1, i) champs = "" Case "Learner Area", "Learner Sub-Area", "Learner Country", "Learner Company", _ "Type", "Job", "Certification Level", "Release", "Reference", "Title", "Start Date" champs = Cells(1, i).Text champs = Replace(champs, " ", "_") champs = Replace(champs, ":", "") champs = Replace(champs, "/", "_") Case "Classroom Duration (Trainee Days)" champs = "Classroom_Duration_Days" Case "E-learning Duration (Hours)" champs = "Elearning_Duration_Hours" Case "Internal/External" champs = "Int_Ext" End Select nbColonnesAfter = nbColonnesAfter + 1 If champs <> "" Then SaveColonnes(1, nbColonnesAfter) = champs End If