Skopírujte z riadka do posledného riadka s údajmi do jedného listu pomocou jazyka VBA v programe Microsoft Excel

Anonim
  • Makro pridá do vášho zošita list s názvom Hlavný a skopíruje bunky z každého listu vo vašom zošite v tomto pracovnom hárku.
  • Prvé makro urobí normálnu kópiu a druhé makro skopíruje hodnoty.
  • Substránky makier používajú nižšie uvedené funkcie, makrá bez nich nebudú fungovať.
Sub CopyFromRow () Dim sh As asheet Dim Dim DestSh As worksheet Dim shLast As Long Dim Last Last Long If SheetExists ("Master") = True Then MsgBox "The sheet master already exist" Exit Sub End If Application.ScreenUpdating = False Set DestSh = Worksheets.Add DestSh.Name = "Master" for each sh In ThisWorkbook.Worksheets If sh.Name DestSh.Name Then If sh.UsedRange.Count> 1 Then Last = LastRow (DestSh) shLast = LastRow (sh) sh.Range ( sh.Rows (3), sh.Rows (shLast)). Copy DestSh.Cells (Last + 1, 1) End If End If Next Application.ScreenUpdating = True End Sub Sub CopyFromRowValues ​​() Dim sh As worksheet Dim DestSh As worksheet Dim shLast as Long Dim Last tak Long If SheetExists ("Master") = True Then MsgBox "The sheet master already exist" Exit Sub End If Application.ScreenUpdating = False Set DestSh = Worksheets.Add DestSh.Name = "Master" for each sh In ThisWorkbook.Worksheets If sh.Name DestSh.Name Then If sh.UsedRange.Count> 1 Then Last = LastRow (DestSh) shLast = LastRow (sh) With sh.Range (sh.Rows (3), sh.Rows (shLast)) DestSh.Cells (Last + 1, 1) .Resize (.Rows.Count, _ .Columns.Count) .Value = .Value End With End If End If Next Application.ScreenUpdating = True End Sub Funkcia LastRow (pracovný list sh As) Pri chybe Pokračovať Ďalej LastRow = sh.Cells.Find (Čo: = "*", _ Potom: = sh.Range ("A1"), _ Lookat: = xlPart, _ LookIn: = xlFormulas , _ SearchOrder: = xlByRows, _ SearchDirection: = xlPrevious, _ MatchCase: = False). Riadok pri chybe GoTo 0 Koniec Funkcia Funkcia Lastcol (pracovný list sh As) Na chybe Pokračovať Ďalší Lastcol = sh.Cells.Find (Čo: = " *", _ After: = sh.Range (" A1 "), _ Lookat: = xlPart, _ LookIn: = xlFormulas, _ SearchOrder: = xlByColumns, _ SearchDirection: = xlPrevious, _ MatchCase: = False). Column On Error GoTo 0 Funkcia funkcie End SheetExists (SName ako reťazec, _ voliteľný ByVal WB ako zošit) Ako booleovský pri chybe Pokračovať ďalej Ak WB nie je nič, potom nastavte WB = ThisWorkbook SheetExists = CBool ​​(Len (Sheets (SName) .Name)) End Function