Copie da linha até a última linha com dados em uma folha usando VBA no Microsoft Excel

Anonim
  • A macro adicionará uma planilha com o nome Master à sua pasta de trabalho e copiará as células de todas as planilhas da pasta de trabalho nesta planilha.
  • A primeira macro faz uma cópia normal e a segunda macro copia os Valores.
  • Os subs da macro usam as funções abaixo, a macro não funcionará sem as funções.
Sub CopyFromRow () Dim sh As Planilha Dim DestSh As Planilha Dim shLast As Long Dim Last As Long If SheetExists ("Master") = True Then MsgBox "A planilha Master já existe" Sair Sub End If Application.ScreenUpdating = False Definir DestSh = Worksheets.Add DestSh.Name = "Master" Para Cada 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)). Copiar DestSh.Cells (Last + 1, 1) End If End If Next Application.ScreenUpdating = True End Sub Sub CopyFromRowValues ​​() Dim sh como planilha Dim DestSh como planilha Dim shLast As Long Dim Last As Long If SheetExists ("Master") = True Then MsgBox "A planilha Master já existe" 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) Com 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 Função LastRow (sh As Worksheet) Em erro Resume Next LastRow = sh.Cells.Find (What: = "*", _ After: = sh.Range ("A1"), _ Lookat: = xlPart, _ LookIn: = xlFormulas , _ SearchOrder: = xlByRows, _ SearchDirection: = xlPrevious, _ MatchCase: = False) .Row On Error GoTo 0 End Function Function Lastcol (sh As Worksheet) On Error Resume Next Lastcol = sh.Cells.Find (What: = " * ", _ After: = sh.Range (" A1 "), _ Lookat: = xlPart, _ LookIn: = xlFormulas, _ SearchOrder: = xlByColumns, _ SearchDirection: = xlPrevious, _ MatchCase: = False) .Column On Error GoTo 0 End Function Function SheetExists (SName As String, _ Opcional ByVal WB As Workbook) As Boolean On Error Resume Next If WB Is Nothing Then Defina WB = ThisWorkbook SheetExists = CBool ​​(Len (Sheets (SName) .Name)) End Function