VBA for formatting stops in specific range

Question

Well I guess it might be a silly question but i could not figure ir out by myself.

I have a WB that has various macros within it. One for copy a template(creating a new WS every time it is necessary) that the user fills ups. After, one macro for coping the results to a 'Summary' WS, then another that applies a formula, after that, one that applies a score (good, bad, ok) and the last one that copies the format of scpecifics cells to give a proper formating to print it.

This last one is acting wierd. I created more than 40 WS (copping the template) but the formatting one stops on the 25th line. I don't know why.

I have used the formula to copy till last row, but nothing can make this thing work.

Bellow follows the result and the code. I thought that the problem could be that I compressed all the command in one sub, so I break it in variuos ones and created a 'trigger' button to activate all of this commands.

If my explaniation is not enought, just ask for more info.

In advance, thanks for all the help!

Erro on the formatting

---

The code I used.

==============

Sub FormatarCab()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

'Using Find Function (Provided by Bob Ulmas)
LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

'Para o cabeçalho
Worksheets("Descritivo").Range("B50").Copy
Worksheets("Avaliação Todos").Range("A1:E1").PasteSpecial xlPasteFormats

Application.CutCopyMode = False
End Sub

'-------------------------------------

Sub FormatarNome()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

'Using Find Function (Provided by Bob Ulmas)
LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

'Para os Nomes das Abas
Worksheets("Descritivo").Range("B52").Copy
Worksheets("Avaliação Todos").Range("A2:A" & LastRow).PasteSpecial xlPasteFormats


Application.CutCopyMode = False
End Sub

'-------------------------------------

Sub FormatarConceito()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

'Using Find Function (Provided by Bob Ulmas)
LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

'Para o Conceito
Worksheets("Descritivo").Range("B54").Copy
Worksheets("Avaliação Todos").Range("E2:E" & LastRow).PasteSpecial xlPasteFormats


Application.CutCopyMode = False
End Sub

'-------------------------------------

Sub FormatarValores()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

'Using Find Function (Provided by Bob Ulmas)
LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

'Para os valoreso
Worksheets("Descritivo").Range("B56").Copy
Worksheets("Avaliação Todos").Range("B2:D" & LastRow).PasteSpecial xlPasteFormats


Application.CutCopyMode = False
End Sub

==============

The sctructure for the function LastRow

==============

Option Explicit

'Common Functions required for all routines:

Function LastRow(Sh As Worksheet)
    On Error 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

==============

Answer 1

Ignoring the LastRow function that you've added to the bottom of the code but don't actually use, you're setting a variable called LastRow with the value of the lowest cell found in sht - which is Set to ActiveSheet.

You then paste down from E2 to E & LastRow - but not necessarily of ActiveSheet - in fact you do this on Worksheets("Avaliação Todos")

You want LastRow to be based on the bottom row of the sheet you're going to paste upon so - it should look like this:

Sub FormatarCab()

    Dim LastRow As Long

    With Worksheets("Avaliação Todos")

        'Using Find Function (Provided by Bob Ulmas)
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

        'Para o cabeçalho
        Worksheets("Descritivo").Range("B50").Copy
        .Range("A1:E1").PasteSpecial xlPasteFormats

    End With

    Application.CutCopyMode = False
End Sub

'-------------------------------------

Sub FormatarNome()

    Dim LastRow As Long

    With Worksheets("Avaliação Todos")

        'Using Find Function (Provided by Bob Ulmas)
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

        'Para os Nomes das Abas
        Worksheets("Descritivo").Range("B52").Copy
        .Range("A2:A" & LastRow).PasteSpecial xlPasteFormats

    End With

    Application.CutCopyMode = False
End Sub

'-------------------------------------

Sub FormatarConceito()

    Dim LastRow As Long

    With Worksheets("Avaliação Todos")

        'Using Find Function (Provided by Bob Ulmas)
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

        'Para o Conceito
        Worksheets("Descritivo").Range("B54").Copy
        .Range("E2:E" & LastRow).PasteSpecial xlPasteFormats

    End With

    Application.CutCopyMode = False
End Sub

'-------------------------------------

Sub FormatarValores()


    Dim LastRow As Long

    With Worksheets("Avaliação Todos")

        'Using Find Function (Provided by Bob Ulmas)
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

        'Para os valoreso
        Worksheets("Descritivo").Range("B56").Copy
        .Range("B2:D" & LastRow).PasteSpecial xlPasteFormats

    End With

    Application.CutCopyMode = False
End Sub

Answer 2

If your looking to find the last row or column you can use the below:

Dim lc As Long
Dim lr As Long

'Change the 1 to whatever row you would want to be able to check for the last true column.
lc = Cells(1, Columns.Count).End(xlToLeft).Column 'determines total number of columns

'Change the "A" to whatever row would show the last row and be consistent for all your worksheets
lr = Range("A" & Rows.Count).End(xlUp).Row 'determines total number of rows including header

The above assumes that you have consistent data