Combine Worksheets and Dynamically Update Information

[KirstenPM]

I have three worksheets, created by three different organizations. The organizations update these worksheets multiple times a day and do not want to combine them, because that would mean 50+ users attempting to access one worksheet to log their changes. I need to have one "master" worksheet that pulls in information from each of these worksheets and as the worksheets are updated, I need the master to dynamically update. I tried the RDMB Merge Add-In, but it doesn't really seem to do the trick for me.

Is there a non-macro way to do this? If macros are the only way to go, can someone help me? I'm short on time and don't know much about writing macros.

Thanks!

 

[Chris Mack]

Are the formats of the three workbooks identical?

What would the master sheet workbook look like?

My idea would be to have a button/macro that would basically rebuild the master sheet every time it was clicked.

 

[KirstenPM]

Are the formats the same? Yes and no. They have agreed to make their first 11 columns contain the same data and in the same order (i.e. Column A is the engineer's name in all three sheets). The master sheet would only contain these first 11 columns and would be in the same order as the feeder sheets.

I'm OK with the idea of having to click a button for the update, but wouldn't have a clue on how to code this.

 

[Chris Mack]

Ok, that sounds like it will be fairly simple.

It might be tomorrow that I get round to it as I've finished work for the day.

Chris

 

[KirstenPM]

Thank you for looking at this

 

[Smitty]

Welcome to the Board!

For a multi-user environment, Access is a much better way to go. Is that an option?

 

[KirstenPM]

This type of thing definitely lends itself to a database, definitely!!! But I'm considered an end-user of the data and the groups are not comfortable with Access and will not use it. We've been stale-mated on this for going on 2 years.

 

[Smitty]

You can make Access as user-friendly as you like, perhaps more so than Excel from a data input standpoint.

Check out some of the Access templates, many of which are easy to modify/deploy.

 

[Chris Mack]

I would tackle this as follows:

Sub Combine()
    wb = ActiveWorkbook.Name
    a = "C:\Workbook1.xlsx"
    b = "C:\Workbook2.xlsx"
    c = "C:\Workbook3.xlsx"
    n = Cells(Rows.Count, 1).End(xlUp).Row
    Rows("1:" & n).Delete
    
    Workbooks.Open(a,,True)
    aname = ActiveWorkbook.Name
    k = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:K" & n).Copy
    Workbooks(wb).Activate
    n = Cells(Rows.Count, 1).End(xlUp).Row + 1
    Cells(n, 1).PasteSpecial Paste:=xlPasteValues
    Workbooks(aname).Close
    
    Workbooks.Open(b,,True)
    bname = ActiveWorkbook.Name
    k = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:K" & n).Copy
    Workbooks(wb).Activate
    n = Cells(Rows.Count, 1).End(xlUp).Row + 1
    Cells(n, 1).PasteSpecial Paste:=xlPasteValues
    Workbooks(bname).Close
    
    Workbooks.Open(c,,True)
    cname = ActiveWorkbook.Name
    k = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:K" & n).Copy
    Workbooks(wb).Activate
    n = Cells(Rows.Count, 1).End(xlUp).Row + 1
    Cells(n, 1).PasteSpecial Paste:=xlPasteValues
    Workbooks(cname).Close
    
    Workbooks(wb).Activate
    Cells(1, 1).Activate
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 1
End Sub

This is the basic concept, it may require tweaking for sheet names, etc.

I would run this code from an ActiveX Button, and it would basically rebuild the worksheet from scratch.

Note that this would be no good if you kept your own comments, etc. on your own master worksheet as these would be deleted.

Hope this helps,

Chris.