Kopieren Sie eine oder mehrere Zeilen aus jeder Arbeitsmappe in einen Ordner mit VBA in Microsoft Excel

Anonim
  • Das Makro kopiert einen Teil des ersten Arbeitsblatts jeder Datei, die sich im Ordner C:\Data befindet, in das erste Arbeitsblatt Ihrer Arbeitsmappe.
  • Das erste Makro macht eine normale Kopie und das zweite Makro kopiert die Werte.
Sub CopyRow() Dim basebook As Workbook Dim mybook As Workbook Dim sourceRange As Range Dim destrange As Range Dim rnum As Long Dim i As Long Dim a As Long Application.ScreenUpdating = False With Application.FileSearch .NewSearch .LookIn = "C:\ Data". mybook.Worksheets(1).Rows("3:5") a = sourceRange.Rows.Count Set desstrange = basebook.Worksheets(1).Cells(rnum, 1) sourceRange.Copy desstrange mybook.Close rnum = i * a + 1 Next i End If End With Application.ScreenUpdating = True End Sub Sub CopyRowValues() Dim basebook As Workbook Dim mybook As Workbook Dim sourceRange As Range Dim destrange As Range Dim rnum As Long Dim i As Long Dim a As Long Application.ScreenUpdating = False Mit Application.FileSearch .NewSearch .LookIn = "C:\Data" .SearchSubFolders = False .FileType = msoFi leTypeExcelWorkbooks If .Execute() > 0 Then Set basebook = ThisWorkbook rnum = 1 For i = 1 To .FoundFiles.Count Set mybook = Workbooks.Open(.FoundFiles(i)) Set sourceRange = mybook.Worksheets(1).Rows( "3:5") a = sourceRange.Rows.Count Mit sourceRange Set desstrange = basebook.Worksheets(1).Cells(rnum, 1). _ Resize(.Rows.Count, .Columns.Count) End With desstrange.Value = sourceRange.Value mybook.Close rnum = i * a + 1 Next i End If End With Application.ScreenUpdating = True End Sub