VB.Net/VBA Copy Rows From Multiple Tabs Into One Sheet in Excel


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2012/06/05/vb-netvba-copy-rows-from-multiple-tabs-into-one-sheet-in-excel/

 

While answering questions in many forums, I very frequently come across questions where the user wants to consolidate rows from all sheets in one sheet. So I finally decided to write a piece of code that I can link to.

You will find below the code for both VB.Net and VBA.


VB.Net (Tested in VS2010/2012 + Office 2010)


Create a form and put a button on the form. Set the reference to Microsoft Excel Library. If you do not know how to do that then see this.

Paste the code and change the file name and output sheet name as required.

The function lets you specify several things like

  • Worksheet for Output
  • Start Row in Output Sheet from where the data needs to be pasted
  • Start Row in Rest of sheets from where the data needs to be copied from
  • Check for Sheet visibility?
  • Paste as Values?
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
' This macro copies data from all sheets into a master sheet '
' '
' Created By Siddharth Rout '
' URL - http://siddharthrout.wordpress.com/2012/06/05/vb-netvba-copy-rows-from-multiple-tabs-into-one-sheet-in-excel/ '
' Date: 05/06/2012 '
' '
' Note: This code can be freely used. However would request '
' that you do not delete these comments '
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim wsO As Excel.Worksheet
    Dim wsISr As Integer, wsILr As Integer, wsOlr As Integer

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Open the relevant Workbook
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsm")

        '~~> Set your worksheet here where the output will be generated
        wsO = xlWorkBook.Sheets("Master")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> IMPORTNAT NOTE: USE ANY ONE OF THE BELOW

        ' This will consolidate rows from all VISIBLE sheets taking '
        ' data from row 5 and output it in Sheets("Master") from row 1 '
        ' onwards. If any cell has formulas then they will be pasted '
        ' as VALUES. '
        '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
        'MergeSheets(wsO, 1, 5, True, True)

        ' This will consolidate rows from ALL sheets taking data from '
        ' row 1 and output it in Sheets("Master") from row 1 onwards. '
        ' If any cell has formulas then they will NOT be pasted as '
        ' values. '
        '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
        MergeSheets(wsO)
        Me.Close()
    End Sub

    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' '
    ' Syntax:- '
    ' wsOutput | Required |: Worksheet for Output '
    ' startRowOutput | Optional |: Start Row in wsOutput '
    ' startRowInput | Optional |: Start Row in Rest of sheets '
    ' chkVisible | Optional |: Check for visiblity? '
    ' pasteVal | Optional |: Paste as Values '
    ' '
    ' USAGE:- '
    ' MergeSheets Sheets("Sheet1"), 10, 5, True, True '
    ' This will consolidate rows from all visible sheets taking '
    ' data from row 5 and output it in Sheets("Sheet1") from row 10 '
    ' onwards. If any cell has formulas then they will be pasted '
    ' as values. '
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
    Private Sub MergeSheets(wsOutput As Excel.Worksheet, _
    Optional startRowOutput As Integer = 1, _
    Optional startRowInput As Integer = 1, _
    Optional chkVisible As Boolean = False, _
    Optional pasteVal As Boolean = False)

        Dim ws As Excel.Worksheet
        wsOlr = startRowOutput
        wsISr = startRowInput
        'If startRowOutput = 0 Then wsOlr = 1 Else wsOlr = startRowOutput
        'If startRowInput = 0 Then wsISr = 1 Else wsISr = startRowInput

        '~~> Loop through all sheets
        For Each ws In xlWorkBook.Sheets
            '~~> Ignore the output sheet
            If ws.Name <> wsOutput.Name Then
                '~~> Visibility Check If Required
                If chkVisible = True And ws.Visible <> Excel.XlSheetVisibility.xlSheetVisible Then GoTo NextSheet

                '~~> Get Last Row of the input sheet
                wsILr = GetLastRow(ws)

                '~~> Check if the last row is greater than [startRowInput]
                '~~> We also check if the sheet is not empty
                If Not wsILr < wsISr Or Not wsILr = 0 Then _
                ws.Rows(wsISr & ":" & wsILr).Copy( _
                wsOutput.Rows(wsOlr))

                '~~> Get the next available row in the output sheet
                wsOlr = GetLastRow(wsOutput) + 1
            End If
NextSheet:
        Next
    End Sub

    '~~> Function to get the last row in the sheet
    Private Function GetLastRow(ByVal wks As Excel.Worksheet) As Long
        GetLastRow = 0
        If xlApp.WorksheetFunction.CountA(wks.Cells) <> 0 Then
            GetLastRow = wks.Cells.Find(What:="*", _
            After:=wks.Range("A1"), _
            LookAt:=Excel.XlLookAt.xlPart, _
            LookIn:=Excel.XlFindLookIn.xlFormulas, _
            SearchOrder:=Excel.XlSearchOrder.xlByRows, _
            SearchDirection:=Excel.XlSearchDirection.xlPrevious, _
            MatchCase:=False).Row
        End If
        Return GetLastRow
    End Function
End Class

VBA (Tested in Office 2010)


Paste this code in a module.

The function lets you specify several things like

  • Worksheet for Output
  • Start Row in Output Sheet from where the data needs to be pasted
  • Start Row in Rest of sheets from where the data needs to be copied from
  • Check for Sheet visibility?
  • Paste as Values?
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
' This macro copies data from all sheets into a master sheet '
' '
' Created By Siddharth Rout '
' URL - http://siddharthrout.wordpress.com/2012/06/05/vb-netvba-copy-rows-from-multiple-tabs-into-one-sheet-in-excel/ '
' Date: 05/06/2012 '
' '
' Note: This code can be freely used. However would request '
' that you do not delete these comments '
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

Option Explicit

Dim wsO As Worksheet
Dim wsISr As Long, wsILr As Long, wsOlr As Long

Sub Sample()
    Dim calc As Long

    On Error GoTo Whoa

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        calc = .Calculation
        .Calculation = xlCalculationManual
    End With

    '~~> Set your worksheet here where the output will be generated
    Set wsO = Sheets("Master")

    '~~> IMPORTNAT NOTE: USE ANY ONE OF THE BELOW

    ' This will consolidate rows from all VISIBLE sheets taking '
    ' data from row 5 and output it in Sheets("Master") from row 1 '
    ' onwards. If any cell has formulas then they will be pasted '
    ' as VALUES. '
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
    MergeSheets wsO, 1, 5, True, True

    ' This will consolidate rows from ALL sheets taking data from '
    ' row 1 and output it in Sheets("Master") from row 1 onwards. '
    ' If any cell has formulas then they will NOT be pasted as '
    ' values. '
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
    MergeSheets wsO

LetsContinue:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = calc
    End With
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
' Syntax:- '
' wsOutput | Required |: Worksheet for Output '
' startRowOutput | Optional |: Start Row in wsOutput '
' startRowInput | Optional |: Start Row in Rest of sheets '
' chkVisible | Optional |: Check for visibility? '
' pasteVal | Optional |: Paste as Values '
' '
' USAGE:- '
' MergeSheets Sheets("Sheet1"), 10, 5, True, True '
' This will consolidate rows from all visible sheets taking '
' data from row 5 and output it in Sheets("Sheet1") from row 10 '
' onwards. If any cell has formulas then they will be pasted '
' as values. '
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
Private Sub MergeSheets(wsOutput As Worksheet, _
    Optional startRowOutput As Long, _
    Optional startRowInput As Long, _
    Optional chkVisible As Boolean, _
    Optional pasteVal As Boolean)

    Dim ws As Worksheet

    If startRowOutput = 0 Then wsOlr = 1 Else wsOlr = startRowOutput
    If startRowInput = 0 Then wsISr = 1 Else wsISr = startRowInput

    '~~> Loop through all sheets
    For Each ws In ThisWorkbook.Sheets
        '~~> Ignore the output sheet
        If ws.Name <> wsOutput.Name Then
            '~~> Visibility Check If Required
            If chkVisible = True And ws.visiBle <> xlSheetVisible Then GoTo NextSheet

            '~~> Get Last Row of the input sheet
            wsILr = GetLastRow(ws)

            '~~> Check if the last row is greater than [startRowInput]
            '~~> We also check if the sheet is not empty
            If Not wsILr < wsISr Or Not wsILr = 0 Then _
            ws.Rows(wsISr & ":" & wsILr).Copy _
            wsOutput.Rows(wsOlr)

            '~~> Get the next available row in the output sheet
            wsOlr = GetLastRow(wsOutput) + 1
        End If
NextSheet:
    Next
End Sub

'~~> Function to get the last row in the sheet
Private Function GetLastRow(ByVal wks As Worksheet) As Long
    If Application.WorksheetFunction.CountA(wks.Cells) <> 0 Then
        GetLastRow = wks.Cells.Find(What:="*", _
        After:=wks.Range("A1"), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Row
    End If
End Function
About these ads

2 responses to “VB.Net/VBA Copy Rows From Multiple Tabs Into One Sheet in Excel

  1. JenAzim June 7, 2012 at 3:18 am

    Nice – I am always looking to consolidate rows from multiple spreadsheets. Thanks.

  2. better networker September 8, 2012 at 10:03 pm

    I have read a few good stuff here. Certainly worth bookmarking for revisiting. I surprise how a lot attempt you place to create this type of fantastic informative web site.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 30 other followers

%d bloggers like this: