Simple Tweak Required for Excel VBA while Copy rows to different sheets

[Jyotirmaya]

Sub CopyRowDemo()
Dim Rw As Long, LstRwRaw As Long, NxtRwDest As Long
Application.ScreenUpdating = False
LstRwRaw = Sheets("Raw Data").Cells(Rows.Count, "B").End(xlUp).Row
For Rw = 3 To LstRwRaw
    With Sheets(Cells(Rw, "B").Value)
        If .Range("B6") = "" Then
            NxtRwDest = 6
        Else
            NxtRwDest = .Cells(Rows.Count, "B").End(xlUp).Row + 1
        End If
        Rows(Rw).Copy .Cells(NxtRwDest, "A")




        With .Cells(NxtRwDest + 1, "G")
            .Formula = "=Sum(G6:G" & NxtRwDest & ")"
            .Font.Bold = True
            .Copy .Offset(, 1).Resize(, 26)
        End With
        
    End With
Next Rw
Application.ScreenUpdating = True
End Sub

I am using the above code to copy rows from a sheet(Raw Data) to different sheets and after copying with the above code it sums the value from G:AG column.

I want some changes in it.
1. It copies entire column to different sheet from the Raw Data Sheet. I want it to be copied from column C to column AH
2. The above code sums from column G:AG, so I want to have a TEXT TOTAL in sum row in F column.

 

[dangelor]

Possibly...

Sub CopyRowDemo()
    Dim Rw As Long
    Dim LstRwRaw As Long
    Dim NxtRwDest As Long
    
    Application.ScreenUpdating = False
    
    LstRwRaw = Sheets("Raw Data").Cells(Rows.Count, "B").End(xlUp).Row
    For Rw = 3 To LstRwRaw
        With Sheets(Cells(Rw, "B").Value)
            If .Range("B6") = "" Then
                NxtRwDest = 6
            Else
                NxtRwDest = .Cells(Rows.Count, "B").End(xlUp).Row + 1
            End If
            Range("C" & Rw & ":AG" & a).Copy .Cells(NxtRwDest, "A")
            
            With .Cells(NxtRwDest + 1, "G")
                .Offset(, -1).Value = "TOTAL"
                .Formula = "=Sum(G6:G" & NxtRwDest & ")"
                .Font.Bold = True
                .Copy .Offset(, 1).Resize(, 26)
            End With
        End With
    Next Rw
    
    Application.ScreenUpdating = True
End Sub

 

[Jyotirmaya]

Possibly...

Sub CopyRowDemo()
    Dim Rw As Long
    Dim LstRwRaw As Long
    Dim NxtRwDest As Long
    
    Application.ScreenUpdating = False
    
    LstRwRaw = Sheets("Raw Data").Cells(Rows.Count, "B").End(xlUp).Row
    For Rw = 3 To LstRwRaw
        With Sheets(Cells(Rw, "B").Value)
            If .Range("B6") = "" Then
                NxtRwDest = 6
            Else
                NxtRwDest = .Cells(Rows.Count, "B").End(xlUp).Row + 1
            End If
            Range("C" & Rw & ":AG" & a).Copy .Cells(NxtRwDest, "A")
            
            With .Cells(NxtRwDest + 1, "G")
                .Offset(, -1).Value = "TOTAL"
                .Formula = "=Sum(G6:G" & NxtRwDest & ")"
                .Font.Bold = True
                .Copy .Offset(, 1).Resize(, 26)
            End With
        End With
    Next Rw
    
    Application.ScreenUpdating = True
End Sub

Hello with the code I am getting Error 400

 

[dangelor]

Which line is highlighted with the error?

 

[Jyotirmaya]

Which line is highlighted with the error?