Kopieer een rij of rijen van elke werkmap in een map met VBA in Microsoft Excel

Anonim
  • De macro kopieert een deel van het eerste werkblad van elk bestand dat zich in de map C:\Data bevindt naar het eerste werkblad van uw werkmap.
  • De eerste macro doet een normale kopie en de tweede macro kopieert de waarden.
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" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks 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 Set destrange = basebook.Worksheets(1).Cells(rnum, 1) sourceRange.Copy destrange mybook.Close rnum = i * a + 1 Volgende 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 With Application.FileSearch .NewSearch .LookIn = "C:\Data" .SearchSubFolders = False .FileType = msoFi leTypeExcelWorkbooks If .Execute() > 0 Stel vervolgens basebook in = 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 Met sourceRange Stel destrange = basebook.Worksheets(1).Cells(rnum, 1) in. _ Resize(.Rows.Count, .Columns.Count) End With destrange.Value = sourceRange.Value mybook.Close rnum = i * a + 1 Next i End If End With Application.ScreenUpdating = True End Sub