none
Macro to sum total on certain columns RRS feed

  • Question

  • Hi 

    I have two tabs "Data" and CopyData".

    "Data" tab has the raw data which I need it copied exactly in "CopyData"

    I have below macro to do this task, and it works. All I need is when it's copies the data from Tab "Data" into Copydata

    it should sum Columns "M" "N" "O" "Q" "R" so I get the totals for these columns at the bottom of each columns.

    Format of totals needs to be BOLD with  separtor and thick borders around the cell. Example 1,000,000

    I will appreciate if someone can provide me macro to do this.

    First row in both Tab is Header and data starts from row 2.

    Sub Tabcopydata()

    Sheets("Data").Select
       Rows("2:2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        
        Sheets("CopyData").Select
        Range("A2").Select
        ActiveSheet.Paste
        
        Sheets("Menu").Select
    End Sub

    Thursday, December 19, 2019 6:19 AM

Answers

  • I have added the requested code:

    Sub Tabcopydata()
        Dim m As Long
        With Worksheets("Data")
            m = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Range("2:" & m).Copy Destination:=Worksheets("CopyData").Range("A2")
        End With
        With Worksheets("CopyData").Range("M" & m + 1 & ":O" & m + 1 & ",Q" & m + 1 & ":R" & m + 1)
            .FormulaR1C1 = "=SUM(R2C:R[-1]C)"
            .NumberFormat = "#,##0"
            .Font.Bold = True
            With .Borders
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
        End With
        With Worksheets("CopyData").Range("B" & m + 1)
            .Value = "GRAND TOTAL"
            .Font.Bold = True
            With .Borders
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
        End With
        Worksheets("Menu").Select
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Romywants Sunday, December 22, 2019 10:51 PM
    Friday, December 20, 2019 10:08 AM

All replies

  • Sub Tabcopydata()
        Dim m As Long
        With Worksheets("Data")
            m = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Range("2:" & m).Copy Destination:=Worksheets("CopyData").Range("A2")
        End With
        With Worksheets("CopyData").Range("M" & m + 1 & ":O" & m + 1 & ",Q" & m + 1 & ":R" & m + 1)
            .FormulaR1C1 = "=SUM(R2C:R[-1]C)"
            .NumberFormat = "#,##0"
            .Font.Bold = True
            With .Borders
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
        End With
        Worksheets("Menu").Select
     End Sub

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, December 19, 2019 12:27 PM
  • Sub Tabcopydata()
        Dim m As Long
        With Worksheets("Data")
            m = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Range("2:" & m).Copy Destination:=Worksheets("CopyData").Range("A2")
        End With
        With Worksheets("CopyData").Range("M" & m + 1 & ":O" & m + 1 & ",Q" & m + 1 & ":R" & m + 1)
            .FormulaR1C1 = "=SUM(R2C:R[-1]C)"
            .NumberFormat = "#,##0"
            .Font.Bold = True
            With .Borders
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
        End With
        Worksheets("Menu").Select
     End Sub

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thank You so much Hans, it worked. Sorry to bother you again, but can you please add "GRAND TOTAL" in Column "B" with Bold formatting and borders around the cell in above code please. I will really appreciate that.
    Friday, December 20, 2019 2:26 AM
  • I have added the requested code:

    Sub Tabcopydata()
        Dim m As Long
        With Worksheets("Data")
            m = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Range("2:" & m).Copy Destination:=Worksheets("CopyData").Range("A2")
        End With
        With Worksheets("CopyData").Range("M" & m + 1 & ":O" & m + 1 & ",Q" & m + 1 & ":R" & m + 1)
            .FormulaR1C1 = "=SUM(R2C:R[-1]C)"
            .NumberFormat = "#,##0"
            .Font.Bold = True
            With .Borders
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
        End With
        With Worksheets("CopyData").Range("B" & m + 1)
            .Value = "GRAND TOTAL"
            .Font.Bold = True
            With .Borders
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
        End With
        Worksheets("Menu").Select
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Romywants Sunday, December 22, 2019 10:51 PM
    Friday, December 20, 2019 10:08 AM
  • It works, you are a Legend. Thanks You Hans. 
    Sunday, December 22, 2019 10:51 PM