Kopirajte vrstico ali vrstice iz vsakega delovnega zvezka v mapo z uporabo VBA v programu Microsoft Excel

Anonim
  • Makro bo kopiral del prvega delovnega lista vsake datoteke v mapi C: \ Data na prvi delovni list vašega delovnega zvezka.
  • Prvi makro naredi običajno kopiranje, drugi makro pa kopira vrednosti.
Sub CopyRow () Dim basebook kot delovni zvezek Dim mybook Kot delovni zvezek 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: \ Podatki ".SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute ()> 0 Potem nastavite basebook = ThisWorkbook rnum = 1 For i = 1 To. 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 Naprej i Končaj, če se konča z Application.ScreenUpdating = True End Sub Sub CopyRowValues ​​() Zatemni osnovno knjigo kot delovni zvezek Zatemni mojo knjigo kot delovni zvezek Zatemni virRange As Range Dim destrange As Range Dim rnum As Long Dim i As Long Dim a As Long Application. = Napačno z Application.FileSearch .NewSearch .LookIn = "C: \ Data" .SearchSubFolders = False .FileType = msoFi leTypeExcelWorkbooks Če je .Execute ()> 0 Potem nastavite basebook = ThisWorkbook rnum = 1 For i = 1 To. Files.Count Set mybook = Workbooks. Open (. "3: 5") a = sourceRange.Rows.Count With sourceRange Nastavi destrange = basebook.Delniki (1). Celice (rnum, 1). _ Spremeni velikost (.Rows.Count, .Columns.Count) Konec z destrange.Value = sourceRange.Value mybook.Close rnum = i * a + 1 Naprej i Konec Če se konča z Application.ScreenUpdating = True End Sub