Re: Take a value (that is summed) in multiple sheets and insert into a master sheet

Question

Re: Creating a master sheet from multiple sheets.

Multiple sheet description: table with many rows and columns. Columns headings are identical but rows vary. Each sheet is a date.

Task: to take a single value from a specific column (always happens to be column M). the value I want is the total of that column. Take this summed value and insert into a master sheet.

My attempt so far is:

Sub append_master_sheet()

    Dim wAppend As Worksheet, wSheet As Worksheet
    Dim LastRow As Long

    Set wAppend = Worksheets("Master")

    For Each wSheet In Worksheets
        If wSheet.Name <> wAppend.Name Then

    LastRow = WorksheetFunction.Max(3, wAppend.Cells(65536, 2).End(xlUp).Row)
    wSheet.UsedRange.Resize(, 13).Copy Destination:=wAppend.Cells(LastRow, 2)

        End If

    Next wSheet

End Sub

1). it takes all 13 columns rather than only the 13th column. (I see that is because I have set it at 13 as I do not know how to cycle through the preceding columns and skip them to only return the 13th column data (and within this column return the total of the column, not the discrete line items

2) Besides returning all the data which is a problem, it actually consistently skips the final value in the column M.

Can you advise how to amend above code to

1) only return the summed value from column M in the multiple sheets (calendar dates) and insert into master.

thanks, N

Use [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) to find the last row — Siddharth Rout, Dec 12 '13 at 19:35

score 0 · Accepted Answer · edited May 23 '17 at 12:11

Is this what you are trying (UNTESTED)

Like I mentioned in the comment above, see THIS link on how to find a last row in a column.

I have commented the code so that you will not have a problem understanding it. But if you do, simply post back :)

Note: I am assuming that the last cell in Col M has the SUM

Option Explicit

Sub append_master_sheet()
    Dim wAppend As Worksheet, wSheet As Worksheet
    Dim wApLRow As Long, wShLRow As Long

    Set wAppend = ThisWorkbook.Worksheets("Master")

    '~~> Get the last row where the ouput should be placed
    wApLRow = wAppend.Range("B" & wAppend.Rows.Count).End(xlUp).Row + 1

    For Each wSheet In Worksheets
        If wSheet.Name <> wAppend.Name Then
            With wSheet
                '~~> Fuind the last row in Col M which has the sum
                wShLRow = .Range("M" & .Rows.Count).End(xlUp).Row
                '~~> Copy over the values to Master Sheet
                wAppend.Range("B" & wApLRow).Value = .Range("M" & wShLRow).Value
                '~~> Increment the row for next output
                wApLRow = wApLRow + 1
            End With
        End If
    Next wSheet
End Sub

Many thanks Siddharth for taking the time to - read my question, provide advise. moreover, adding in comments to explain the purpose of the changes you introduce to make the code work is hugely appreciated. thank you — user3047291, Dec 13 '13 at 09:28

Re: Take a value (that is summed) in multiple sheets and insert into a master sheet

1 Answers1