Copia una riga o righe da ciascuna cartella di lavoro in una cartella utilizzando VBA in Microsoft Excel

Anonim
  • La macro copierà una parte del primo foglio di lavoro di ogni file che si trova nella cartella C:\Data nel primo foglio di lavoro della cartella di lavoro.
  • La prima macro esegue una copia normale e la seconda macro copia i valori.
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 Imposta basebook = ThisWorkbook rnum = 1 For i = 1 To .FoundFiles.Count Imposta mybook = Workbooks.Open(.FoundFiles(i)) Imposta sourceRange = mybook.Worksheets(1).Rows("3:5") a = sourceRange.Rows.Count Imposta destrange = basebook.Worksheets(1).Cells(rnum, 1) sourceRange.Copy destrange 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 = Falso con Application.FileSearch .NewSearch .LookIn = "C:\Data" .SearchSubFolders = Falso .FileType = msoFi leTypeExcelWorkbooks If .Execute() > 0 Then Imposta basebook = ThisWorkbook rnum = 1 For i = 1 To .FoundFiles.Count Imposta mybook = Workbooks.Open(.FoundFiles(i)) Imposta sourceRange = mybook.Worksheets(1).Rows( "3:5") a = sourceRange.Rows.Count With sourceRange Imposta destrange = basebook.Worksheets(1).Cells(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