Kopējiet rindu vai rindas no katras darbgrāmatas mapē, izmantojot Microsoft Excel VBA

Anonim
  • Makro kopēs katra darblapas pirmās darblapas daļu, kas atrodas mapē C: \ Data, uz jūsu darbgrāmatas pirmo darblapu.
  • Pirmais makro veic parastu kopiju, bet otrs makro kopē vērtības.
Sub CopyRow () Dim bāzesgrāmata kā darbgrāmata Dim mybook kā darbgrāmata 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: \ Dati ".SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute ()> 0 Tad 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). Šūnas (rnum, 1) sourceRange.Copy destrange mybook.Close rnum = i * a + 1 Nākamais i Beigas Ja beidzas ar lietojumprogrammu.ScreenUpdating = Patiesa beigas Sub Sub CopyRowValues ​​() Dim bāzesgrāmata kā darbgrāmata Dim mybook kā darbgrāmatas Dim avots Diapazons kā diapazons Dim attālums kā diapazons Dim rnum Cik garš Dim kā tik garš Dim kā tik ilgi lietojumprogramma. = False ar Application.FileSearch .NewSearch .LookIn = "C: \ Data" .SearchSubFolders = False .FileType = msoFi leTypeExcelWorkbooks If .Execute ()> 0 Tad 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 Ar sourceRange Iestatiet destrange = basebook.Worksheets (1). Cell (rnum, 1). _ Mainīt izmērus (.Rows.Count, .Columns.Count) Beigt ar destrange.Value = sourceRange.Value mybook.Close rnum = i * a + 1 Next i End if End with Application.ScreenUpdating = True End Sub