CODEWITHSUNDEEP
excelvba
mik.ro
mik.ro

Reputation: 4893

merge multiple worksheets into one

I'm trying to merge multiple worksheets into one summary sheet.
Each Worksheet has the name 'Table #number', for example Table 1, Table 2 etc. The layout of each sheet is identical. Data range is columns A1 : N13.
This function doesn't work: =SUM('Table 1':'Table 25'!$A$1:$N$13).
How do I use VBA to amalgamate this data?

enter image description here

Upvotes: 0

Views: 4700

Answers (2)

Vitalie Bancu
Vitalie Bancu

Reputation: 181

Sub MergeSheet()

'Declaring the Variables
Dim LastRow, ShtCnt As Integer
Dim ShtName As String
Dim NewSht As Worksheet

'Assinging a Sheet Name by UserInput
ShtName:
ShtName = InputBox("Enter the Sheet Name you want to create", "Merge Sheet", "Master Sheet")

'Count of Total Worksheet in the present workbook
ShtCnt = Sheets.Count

'Using For Loop check if the worksheet exists
For i = 1 To ShtCnt
If Sheets(i).Name = ShtName Then
MsgBox "Sheet already Exists", , "Merge Sheet"
GoTo ShtName
End If
Next i

'Create a New Sheet
Worksheets.Add.Name = ShtName

'Assigning NewSht as Current Sheet
Set NewSht = ActiveSheet

'Moving Worksheet to the beginning of this workbook
NewSht.Move before:=Worksheets(1)

'Copying all the data to the New Sheet Using For Loop
For i = 2 To ShtCnt + 1

'If i=2 Then copy all the data from the second sheet including header.
If i = 2 Then
Sheets(i).UsedRange.Copy NewSht.Cells(1, 1)
Else

'If i is grater than 2 then copy all the data excluding Header(1st Row).
Sheets(i).UsedRange.Offset(1, 0).Resize(Sheets(i).UsedRange.Rows.Count - 1, Sheets(i).UsedRange.Columns.Count).Copy NewSht.Cells(LastRow + 1, 1)
End If
LastRow = NewSht.Cells.SpecialCells(xlCellTypeLastCell).Row
Next i

'Displaying the Message after copying data successfully
MsgBox "Data has been copied to " & ShtName, , "Merge Sheet"

End Sub

Upvotes: 1

whytheq
whytheq

Reputation: 35557

This is a simplified example: 

Option Explicit

Sub amalgamateData()

'initialise result variable
Dim myResult As Double
myResult = 0

'loop through sheets to get the sum
Dim wks As Excel.Worksheet  'loop control variable
For Each wks In Excel.ThisWorkbook.Worksheets
    If Left(wks.Name, 5) = "Table" Then ' only the "Table" sheets
        With wks
            Dim rngTarget As Range
            myResult = myResult + Excel.Application.WorksheetFunction.Sum(.Range("A1:N13"))
        End With
    End If
Next

'add result to sheet "Result"
Excel.ThisWorkbook.Sheets("Result").Range("A1") = myResult

End Sub

My starting point was this SO Post: how-to-merge-data-from-multiple-sheets

As Siddharth saud - there loads of references for you on SO HERE IS A SEARCH FOR YOU ... CHECK OUT WHAT IS IN THE BOX IN THE TOP RIGHT OF THE SCREEN

Upvotes: 0

Related Questions