AutoSum Error

[CrispyAsian]

Hey everyone,

I'm having an issue and I have no idea why it's happening. I currently have a pretty large macro running, but at one part it's supposed to find the last row, autosum the contents, add the word "Total" to the first column and delete anything in the second column. Here's the section of code that it's running:

    With Sheets("Co-Pilot").Range("a1").CurrentRegion
        If .Cells(.Rows.Count, 1).Value <> "Total" Then
            With .Offset(.Rows.Count).Resize(1)
                .Formula = "=sum(r2c:r[-1]c)"
                .Columns(1).Value = "Total"
                .Columns(2).Value = ""
            End With
        End If
    End With

The issue is, when I run the macro, for some reason this happens:

Why do columns C-F not have the AutoSum formula in them? Any ideas?

 

[Fluff]

Could you please show us the whole code?
The portion you've posted works for me, so there may be something else in the code that's causing the problem

 

[CrispyAsian]

Sure thing!

Sub CopyCopilot()
    
    Sheets("Co-Pilot").UsedRange.ClearContents
    
    Worksheets("Master").Range("B2:BR2").Copy Worksheets("Co-Pilot").Range("B1")
    
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Master")
    Set Target = ActiveWorkbook.Worksheets("Co-Pilot")
    
        
    Dim lastVal As String
    j = 2     ' Start copying to row 2 in target sheet
    For Each c In Source.Range("A3:A1000")   ' Do 1000 rows
        If c = "C" Or (c.Value = "" And lastVal = "C" And c.MergeCells) Then
            Source.Rows(c.Row).Copy Target.Rows(j)
            Target.Cells(j, 1).Value = "C" ' need to set because of merged cells
            j = j + 1
        End If
        If c.Value <> "" Then
            lastVal = CStr(c)
        End If
    Next c
    
    Dim r As Range, x As Range
    With Application.FindFormat
        .Clear
        .Interior.Color = vbBlack
    End With
    With ActiveWorkbook.ActiveSheet
        Do
            Set r = .Cells.Find("*", , , , , , , , True)
            If Not r Is Nothing Then
                r.EntireColumn.Delete
            Else
                Exit Do
            End If
        Loop
        For Each r In .Cells(1).CurrentRegion.Rows(2).Cells
            If r.Value = "" Then
                If x Is Nothing Then
                    Set x = r.EntireColumn
                Else
                    Set x = Union(x, r.EntireColumn)
                End If
            End If
        Next
        If Not x Is Nothing Then x.Delete
    End With
       
    With Sheets("Co-Pilot").Range("a1").CurrentRegion
        If .Cells(.Rows.Count, 1).Value <> "Total" Then
            With .Offset(.Rows.Count).Resize(1)
                .Formula = "=sum(r2c:r[-1]c)"
                .Columns(1).Value = "Total"
                .Columns(2).Value = ""
            End With
        End If
    End With
    
    Dim N As Long
    N = Cells(Rows.Count, "A").End(xlUp).Row
        With Cells(N, "A").Font
        .Name = "Arial"
        .Size = 12
        .Bold = True
        .Underline = xlUnderlineStyleSingle
        End With
    
   ActiveSheet.Columns.ColumnWidth = 12
   ActiveSheet.Rows.RowHeight = 45
   
End Sub

Like I said, it's a big macro. There's something messing it up in there. It's just so much I can't figure out what part is doing it.

 

[Fluff]

There's nothing after you had the totals, that would remove them.
Whilst from the image you posted, it looks ok, but check that those cells aren't merged.
Also check that the font colour hasn't been set to white.

 

[CrispyAsian]

Welp...that's embarrassing. Thank you for pointing out what should have been obvious. Who knows how the text got turned white, but the simplest answer is usually the correct one right. Thanks for the help!

 

[Fluff]

Glad to help & thanks for the feedback