Kopier en rad eller rader fra hver arbeidsbok i en mappe ved hjelp av VBA i Microsoft Excel

Anonim
  • Makroen kopierer en del av det første regnearket i hver fil i mappen C: \ Data til det første regnearket i arbeidsboken.
  • Den første makroen gjør en normal kopi og den andre makro kopierer verdiene.
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 Sett deretter basebook = ThisWorkbook rnum = 1 For i = 1 Til .FoundFiles.Count Set mybook = Workbooks.Open (.FoundFiles (i)) Angi 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 Neste i End If End with Application.ScreenUpdating = True End Sub Sub CopyRowValues ​​() Dim basebok 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 = Falsk Med Application.FileSearch .NewSearch .LookIn = "C: \ Data" .SearchSubFolders = Falsk .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 With sourceRange Sett destrange = basebook.Worksheets (1) .Celler (rnum, 1). _ Endre størrelse (.Rows.Count, .Columns.Count) Slutt med destrange.Value = sourceRange.Value mybook.Close rnum = i * a + 1 Neste i End If End with Application.ScreenUpdating = True End Sub