Kopiér en række eller rækker fra hver projektmappe i en mappe ved hjælp af VBA i Microsoft Excel

Anonim
  • Makroen kopierer en del af det første regneark i hver fil, der er i mappen C: \ Data, til det første regneark i din projektmappe.
  • Den første makro kopierer en normal kopi og den anden makro kopierer værdierne.
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 = Falsk .FileType = msoFileTypeExcelWorkbooks If .Execute ()> 0 Indstil derefter basebook = ThisWorkbook rnum = 1 For i = 1 Til .FoundFiles.Count Set mybook = Workbooks.Open (.FoundFiles (i)) Indstil 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 Næste 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 = Falsk med Application.FileSearch .NewSearch .LookIn = "C: \ Data" .SearchSubFolders = Falsk .FileType = msoFi leTypeExcelWorkbooks If .Execute ()> 0 Indstil derefter 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 Med sourceRange Indstil destrange = basebook.Worksheets (1). Celler (rnum, 1). _ 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