Copier une ou plusieurs colonnes de chaque classeur dans un dossier en utilisant VBA dans Microsoft Excel
-
La macro copiera une partie de la première feuille de calcul de chaque fichier qui se trouve dans le dossier C: \ Data dans la première feuille de calcul de votre classeur.
-
La première macro fait une copie normale et la seconde macro copie les valeurs.
Rappelez-vous qu’Excel n’a que 256 colonnes
Sub CopyColumn ()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim cnum As Integer
Dim i As Long
Dim a As Integer
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set basebook = ThisWorkbook
cnum = 1
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
Set sourceRange = mybook.Worksheets(1).Columns("A:B")
a = sourceRange.Columns.Count
Set destrange = basebook.Worksheets(1).Cells(1, cnum)
sourceRange.Copy destrange
mybook.Close
cnum = i * a + 1
Next i
End If
End With
Application.ScreenUpdating = True
End Sub
Sub CopyColumnValues()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim cnum As Integer
Dim i As Long
Dim a As Integer
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set basebook = ThisWorkbook
cnum = 1
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
Set sourceRange = mybook.Worksheets(1).Columns("A:B")
a = sourceRange.Columns.Count
With sourceRange
Set destrange = basebook.Worksheets(1).Columns(cnum). _
Resize(, .Columns.Count)
End With
destrange.Value = sourceRange.Value
mybook.Close
cnum = i * a + 1
Next i
End If
End With
Application.ScreenUpdating = True
End Sub