Several Worksheets into one master

caz46 · #1 05-11-2018, 06:20 AM

Hello,

I have 5 different workbooks which are edited by different users, i.e. Cars, Bikes, Vans HGV, Other.

I then have a master workbook with 5 worksheets, one for each of the workbooks above. I regularly have to open each individual WB and update my master one.

Is there a way to automate this so that my master is updated each time one of the individual workbooks are amended and saved?

Also, would they all have to be on the same network file location?

Many thanks in advance for your valuable help.
Caz

ArviLaanemets · #2 05-11-2018, 07:23 AM

Create ODBC queries to get tables from specified workbook into specified sheet {Source tables must be properly designed - must have a single header row with headers for every column, preferably don't have gaps row- or columnwise, all columns must have proper data (i.e. not so that e.g. in Producer column you have "Ford", and then the column is empty until next car Producer is entered), etc.}. Set queries to be refreshed when the Master workbook is opened.

Query reads saved data from source workbook. When you opened the Master workbook, and some user edits source workbook afterwards, you can't get edited data until the other user doesn't save his/her workbook. But this is so with all other ways too - you can access other workbooks saved somewhere. When user opens workbook, Excel creates a virtual copy of it, and user makes all changes in this virtual copy until the workbook is saved. (Of course you can set autosave on for source workbooks.)

When you set the query to be refreshed on open, as I adviced, the master workbook gets latest saved data from source workbooks. When you have the master workbook open for longer time, you can always refresh all queries (or specific query) at will. And you can additionally set every query separately to be refreshed at wanted time interval - but this may be quite annoying when suddenly your workbook freezes when you are halfway in entering something.

Queries may work in other way too. You can have some general tables on Master workbook, which you keep up to date, and other users can read those tables into their workbooks, where thy may be used e.g. as sources for data validation lists, or user workbook has some formulas to get additional info into his/her table. The main rule is - data are entered only in one workbook and all other workbooks can only read this data. I myself usually hide sheets with queried data from user to avoid something messed up accidently.

Shashi Kant · #3 05-11-2018, 11:13 PM

Code:

Option Explicit
Option Base 1

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    
    Dim wkb As Workbook
    Dim i As Integer
    Dim wbName(5) As String
    
    wbName(1) = "Car.xlsx"
    wbName(2) = "Bikes.xlsx"
    wbName(3) = "HGV.xlsx"
    wbName(4) = "Vans.xlsx"
    wbName(5) = "Other.xlsx"
    
    For i = 1 To 5
        Set wkb = Workbooks.Open(ThisWorkbook.Path & "\" & wbName(i))
    
        wkb.Worksheets(1).Range("A1").CurrentRegion.Offset(1, 0).Select
        Selection.Resize(Selection.Rows.Count - 1).Select
    
        Selection.Copy ThisWorkbook.Worksheets(i).Range("A2")
        wkb.Close savechanges:=True
    Next i
    Application.ScreenUpdating = True
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Master tab data list parsed out to applicable cells in applicable worksheets	kiwimtnbkr	Excel Programming	11	10-28-2017 01:38 PM
Editable text box in master slide not appearing once closing master slide mode	tparnicott	PowerPoint	1	06-17-2016 04:13 AM
How do I set default workSHEETs?	CanvasShoes	Excel	1	08-03-2015 02:39 AM
Add worksheets to workbook	boboles	Excel	2	01-26-2015 07:42 AM
Linking Workbooks/Entire Worksheets to Identical in Master	taxacct	Excel	2	10-01-2014 11:22 AM