Concatenate multiple worksheets into master worksheet

[scottbass]

Hi,

I know this is probably a FAQ, and for that I apologize. I did Google extensively before posting, but couldn't find exactly what I was looking for. If the code is somewhere I couldn't find, please just point me in the right direction. Thanks...

There are two potential business scenarios:

1) We have a single workbook with multiple worksheets. Worksheet1 is the "Master" worksheet. Worksheets2 - N are one per end user, say named by their LAN userid (the name isn't really relevant). Each end user will input his/her data into "their" worksheet. All worksheets except master will have the same structure: row 1 has column names, rows 2+ will contain data, column names and positions are identical across worksheets. This workbook will be shared for concurrent editing, with history turned off to reduce/eliminate workbook bloating.

The master worksheet will have a button to "Refresh Data". This will invoke a VBA macro to spin through worksheets 2 - N, copying the "active data" (i.e. cells actually containing something) in each workbook, concatenating the data into the master worksheet.

In pseudocode:

* get number of worksheets
* loop over worksheet2 - worksheetN
* activate A2
* search to last cell
* copy this range
* back to master worksheet
* activate A2 (for initial paste only)
* paste
* advance one row (so now in A#)
* rinse and repeat

The end result would be as if I'd selected each worksheet, copied the active data, and pasted into the master workbook, one after the other, with no blank lines between each paste.

2) Virtually identical to above, but each user's data is in a separate workbook, with only a single worksheet. Otherwise, identical processing as above. I'm happy if the master workbook and end user workbooks are in different directories, and the end user directory can only contain the relevant workbooks. If we want to implement a particular concatenation order, I'm happy to require that the workbooks have a file naming prefix, i.e. 01_userA.xlsx, 02_userB.xlsx, etc.

In pseudocode:

* get list of all workbooks (*.xls, *.xlsx, etc) in the end user directory.
* loop over each workbook
* process sheet1 in each workbook
* (then same processing as above)

My preference is scenario #1, but this is still being debated.

The actual business scenario is a very crude project management application (don't ask). So, each user inputs what they worked on each week: date, task, hours consumed, etc. The big manager then gets the concatenated data from the worker bees. I know there are numerous ways to do this better (JIRA, web tools, etc), but this is what they want.

Last nice to have: if we can filter the concatenated data, that would be super. So, spin through the end user worksheets, get only rows where date is between 22Jul13 and 28Jul13, and only concatenate that data. The manager would specify the start and end date in a couple of cells in the master workbook.

But at this point I'm happy for just a simple concatenation macro to get me started.

Thanks,
Scott

 

[scottbass]

Hi,

I know this is probably a FAQ, and for that I apologize. I did Google extensively before posting, but couldn't find exactly what I was looking for. If the code is somewhere I couldn't find, please just point me in the right direction. Thanks...

There are two potential business scenarios:

1) We have a single workbook with multiple worksheets. Worksheet1 is the "Master" worksheet. Worksheets2 - N are one per end user, say named by their LAN userid (the name isn't really relevant). Each end user will input his/her data into "their" worksheet. All worksheets except master will have the same structure: row 1 has column names, rows 2+ will contain data, column names and positions are identical across worksheets. This workbook will be shared for concurrent editing, with history turned off to reduce/eliminate workbook bloating.

The master worksheet will have a button to "Refresh Data". This will invoke a VBA macro to spin through worksheets 2 - N, copying the "active data" (i.e. cells actually containing something) in each workbook, concatenating the data into the master worksheet.

In pseudocode:

* get number of worksheets
* loop over worksheet2 - worksheetN
* activate A2
* search to last cell
* copy this range
* back to master worksheet
* activate A2 (for initial paste only)
* paste
* advance one row (so now in A#)
* rinse and repeat

The end result would be as if I'd selected each worksheet, copied the active data, and pasted into the master workbook, one after the other, with no blank lines between each paste.

2) Virtually identical to above, but each user's data is in a separate workbook, with only a single worksheet. Otherwise, identical processing as above. I'm happy if the master workbook and end user workbooks are in different directories, and the end user directory can only contain the relevant workbooks. If we want to implement a particular concatenation order, I'm happy to require that the workbooks have a file naming prefix, i.e. 01_userA.xlsx, 02_userB.xlsx, etc.

In pseudocode:

* get list of all workbooks (*.xls, *.xlsx, etc) in the end user directory.
* loop over each workbook
* process sheet1 in each workbook
* (then same processing as above)

My preference is scenario #1, but this is still being debated.

The actual business scenario is a very crude project management application (don't ask). So, each user inputs what they worked on each week: date, task, hours consumed, etc. The big manager then gets the concatenated data from the worker bees. I know there are numerous ways to do this better (JIRA, web tools, etc), but this is what they want.

Last nice to have: if we can filter the concatenated data, that would be super. So, spin through the end user worksheets, get only rows where date is between 22Jul13 and 28Jul13, and only concatenate that data. The manager would specify the start and end date in a couple of cells in the master workbook.

But at this point I'm happy for just a simple concatenation macro to get me started.

Thanks,
Scott

Can someone at least confirm that this is theoretically possible? If so, I've got access to the book "Excel 2007 Power Programming with VBA" by John Walkenbach, so I can muddle through this task myself. It's just I thought that this was a common request, and that the code to do (at least the bulk) of this task was already available somewhere.

 

[=ODIN=]

both scenarios are possible.

scenario 1 fairly easy. scenario 2 is much more complex, but doable.

i'll write you some code for scenario one right now, all i need is for you to tell me which column on your sheets is guaranteed to have a value in it at the vary last row of any data.

 

[=ODIN=]

Scenario 1 is below.
You Need to change 2 things to get it working.
1. change "A" in it to the column I asked oyu for earlier.
2. change the sheet1 bit in

Set mshiZ = Sheet1

to be the correct codename of you designated master sheet.

Assumptions:
only works on visible sheets
only works on sheets that have < as the first character of their name (this way you can have other sheets in the book that are ignored by macro
copies data from data sheets in blocks, so i they leave spacer rows in the data, those come with the data to the master.

Sub Convergence()
    
    Dim mshiZ   As Worksheet, dshiz As Worksheet, mLR As Long, dLR As Long, mFR As Long, dFR As Long, tRows As Long, coL As String
    
    coL = "A"
    
    Set mshiZ = Sheet1
    mFR = 2
    dFR = 2
    
    mshiZ.Cells.EntireColumn.Hidden = False
    mshiZ.Cells.EntireRow.Hidden = False
    tRows = mshiZ.Rows.Count
    
    mshiZ.AutoFilterMode = False
    mshiZ.Rows(mFR & ":" & tRows).Clear
    
    For Each dshiz In ActiveWorkbook.Worksheets
        With dshiz
            .Cells.EntireColumn.Hidden = False
            .Cells.EntireRow.Hidden = False
        If .CodeName <> mshiZ.CodeName And .Visible = xlSheetVisible And InStr(1, Left(.Name, 1), "<", vbTextCompare) <> 0 Then
            .AutoFilterMode = False
            If .Range(coL & tRows).End(xlUp).Row + 1 <> dFR Then
                dLR = .Range(coL & tRows).End(xlUp).Row
                mLR = mshiZ.Range(coL & tRows).End(xlUp).Row + 1
                
                .Rows(dFR & ":" & dLR).Copy Destination:=mshiZ.Range("a" & mLR)
                
            
            End If
        
        End If
        End With
    Next dshiz
    
End Sub

Oh, and as for filtering. instead of trying to filter the data then pull it to the master, it is far easier to just pull it all to the master with the code above, then apply your filters once to the new master data set -- you can get rid of spacer rows etc..... using the macro recorder, you can probably get yourself some decent base code for any filters you want.

 

[Smitty]

Welcome to the Board!

If all of the sheets are structured identically, why not use a Pivot Table with multiple consolidation ranges? Then all you need to do is refresh the Pivot Table to get the most current data. And you can filter on the dates that you want.

HTH,

 

[scottbass]

Welcome to the Board!

If all of the sheets are structured identically, why not use a Pivot Table with multiple consolidation ranges? Then all you need to do is refresh the Pivot Table to get the most current data. And you can filter on the dates that you want.

HTH,

Excel 2007...

Ok, so I went to Data Tab --> Consolidate. I'd prefer no summary function at all; as stated, I just want all rows from all the desired worksheets to be concatenated in the master worksheet. Nevertheless, I accepted the default of Sum, and added 4 worksheets to the multiple consolidation (clicked the upper left corner to select the entire range).

Created a new worksheet, selected Insert --> Pivot Table. I don't see how to bind the consolidated range as the data source.

Google is my friend, but if you can provide further info...

 

[Smitty]

See: Excel Pivot Table Tutorial -- Multiple Consolidation Ranges

 

[gstreff]

both scenarios are possible.

scenario 1 fairly easy. scenario 2 is much more complex, but doable.

i'll write you some code for scenario one right now, all i need is for you to tell me which column on your sheets is guaranteed to have a value in it at the vary last row of any data.

Hi
Was looking for a way to concatenate a bunch of tabs in an Excel sheet and came across this thread. I am not a VB or even a good macro writer in Excel. Wondering if I could get you to work on a file for me? Looks like what this thread describes is what will work for mine. I have 1067 rows with exactly the same columns spread across 521 tabs. Biggest problem is there is a header of varying numbers of rows in each tab. There is a unique "Item #" column in each tab. Just need to pull all 1067 rows to one tab.
Any chance you can help?
Thanks. Removed E-Mail Address - Moderator
GAry