Combining Worksheets

Post date: Jun 8, 2014 6:51:24 PM

Why read this:

Need to combine data with identical layouts from multiple worksheets? Learn how.

Background:

It is common for XL users to take data, such as customer data, and use one worksheet for each customer; or one worksheet for each G/L account's entries; or one worksheet for each project, or... you get the idea. It is also common to have multiple workbooks given to multiple users for each user to make entries, such as each worker's time, or each partner's billable hours, or each sales person's sales.

At some point this data must be combined for reporting or analysis.

XL provides a "Consolidate" utility that works for some situations. XL also provides MS Query which can combine worksheets with an SQL UNION operator. But most XL users don't know SQL.

Solution:

The routine below reads entries from an XL table (see figure above) to find worksheets and combine them by creating and executing an SQL statement with all necessary UNIONs. We can combine as many worksheets as we like from as many workbooks as needed. All we have to do is enter the worksheet's path.

Demo:

Download this zip file (URL is below) containing sample workbooks and Consolidate.xlsm. Consolidate.xlsm has the VBA code (Macro). To use the zip file, we MUST follow these steps in order

• 1. Download the zip

  2. Open the download folder

  3. Right click on the zip and select Extract All (DO NOT double click the zip and expect things to run. They won't).

  4. Choose the directory to extract everything to and make a note of it

  5. Open that directory with all extracted files.

  6. Double click Consolidate.xlsm

  7. Click the Consolidate button and see what happens.

  8. Open the VBE (ALT-F11) and look in the immediate window (CTRL-G) to see the generated SQL statement.

URL: https://www.dropbox.com/s/662khme8x4lkogq/Consolidate.zip?dl=1

Notes!

There are a couple of things we must pay attention to in the XL Table.

• 1. The apostrophes in paths are NOT apostrophes. They are accents (US Keyboards, upper left corner below Esc).

  2. `Sheet1$` is the first worksheet's common name. If your data is in the second worksheet we would use `Sheet2$` unless the worksheet is named "SALES" in which case we would use `SALES$`.

  3. I recommend naming ranges instead of using worksheet names. If we named our data range "Customer A" we would use `Customer A` instead of `Sheet1$` (the $ symbol indicates a sheet name instead of a named range)

  4. XL Table names cannot be used as range names but we can apply a name to a table and that works nicely AND the named range becomes every bit as dynamic as the table name.

  5. The code below will substitute the word "<Path>" with the current workbook's path (which is why we can't run this from the zip file without extracting the files to a permanent directory).

Code:

Sub Consolidate()

Dim sSQL As String 'SQL String

Dim oLr As ListRow 'Worksheets Row

Dim oCn As Object 'Connection

Dim oRs As Object 'Recordset

' Create SQL

For Each oLr In Sheet1.ListObjects("Worksheets").ListRows

If sSQL <> "" Then sSQL = sSQL & vbCr & "Union " & vbCr

sSQL = sSQL & "Select * From " & oLr.Range(1)

Next

sSQL = Replace(sSQL, "<Path>", ThisWorkbook.Path)

' Create Connection Objects

Set oCn = CreateObject("ADODB.Connection")

Set oRs = CreateObject("ADODB.Recordset")

oCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & ThisWorkbook.FullName & ";" & _

"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

oRs.Open sSQL, oCn

Debug.Print sSQL

If Sheet1.ListObjects.Count > 1 Then Sheet1.ListObjects(2).Delete

Sheet1.ListObjects.Add( _

SourceType:=xlSrcQuery, _

Source:=rs, _

Destination:=Sheet1.Range("C6")).QueryTable.Refresh

oRs.Close

oCn.Close

Set oRs = Nothing

Set oCn = Nothing

End Sub