Skopírujte riadok alebo riadky z každého zošita v priečinku pomocou jazyka VBA v programe Microsoft Excel

Anonim
  • Makro skopíruje časť prvého pracovného hárka každého súboru, ktorý je v priečinku C: \ Data, do prvého pracovného hárka vášho zošita.
  • Prvé makro skopíruje normálne a druhé makro skopíruje hodnoty.
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: \ Údaje ".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 Ďalej i Koniec, ak sa skončí s Application.ScreenUpdating = True End Sub Sub CopyRowValues ​​() Dim basebook As Workbook Dim mybook As Workbook Dim sourceRange As Range Dim distrange As Range Dim rnum As Long Dim i As Long Dim i as Long Application.ScreenUpdating = False With 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 With sourceRange Set destrange = basebook.Worksheets (1) .Cells (rnum, 1). _ Zmena veľkosti (.Rows.Count, .Columns.Count) Koniec s destrange.Value = sourceRange.Value mybook.Close rnum = i * a + 1 Next i End If End With Application.ScreenUpdating = True End Sub