Combining multiple workbooks into one master workbook

[Chynky77]

[FONT=&quot]We get a lot of quotes from vendors in our company from all over the country. We want to track the quotes we are getting so we can start getting a better picture as to pricing trends.[/FONT]
[FONT=&quot]Every rep has an excel file called QT (Name). Sheet 1 is where they are inputting the quotes they are getting. There are other sheets in the workbook but I'm only interested in the data in Sheet1. I have a QT Master which is the sheet I want to have all the data sent to. All of the Excel files are setup exactly the same. A-H are used with the 1st row being the categories. I want the data to continuously update the master workbook as the reps are receiving more quotes. I don't want the data to overwrite but rather to just keep entering

I am sorry if the question is confusing. I am a complete noob to this.

[/FONT]
[FONT=&quot]I have purchased 3 kindle books. Watched dozens of youtube videos. I just can't seem to get it. Please help me[/FONT]

 

[frabulator]

The easiest way that I can think of would be to reference the external workbooks on your master workbook. Every time you open the master it will ask you if you would like to update the references which will update all the values for each reference.

Input the following formula into your cell, changing the directories as needed.

='C:\Documents and Settings\USER\Desktop\[Book1.xlsx]Sheet1'!A1

the break down:
='DIRECTORY\[NAME OF WORKBOOK.xlsx]SHEET'!CELL REFERENCE


---EDIT---

I don't want the data to overwrite but rather to just keep entering

I just registered that. Do you mean that you want a workbook to contains EVERY entry made on every sheet? Thus creating a datasheet that keeps all entered data regardless if it has been updated/changed/erased from the source workbooks?

 

[Chynky77]

Yes that is what I want. They call vendors and get fee quotes. I want all the quotes entered into an excel sheet. That excel sheet then updates the main workbook which will contain all of the reps quotes. After a while we will be able to see what everyone is quoting in a market and then adjust our pricing accordingly if that makes sense

 

[frabulator]

What I forsee being a problem is the fact that you have more than one person needing to dump data into a workbook at one time. I also see that people are going to be using the same datasheet everytime they create and invoice. Because of this you can not use the method I suggested before as every time they open the workbook to make an adjustment they will be overwriting their old/last data. What I would suggest would be to have a macro assigned on each one of the employees excel's to update a master when they are ready to submit an invoice.

What I have below is an example of what I would do. You will need to modify the code to fit your needs, but this should give you a good idea of the direction I would take. If you would like to make it easier on the users I would suggest looking into making an excel addin that you can use in our office. It would be a little bit more coding on your end, but the end user would simply need to click a button.
(Just a thought)

Option Explicit

Sub Export()


    'this gets the name of the person that is using excel
    Dim WorkerName As String
    'this gets the current date
    Dim DT As String
    'this gets the name of the master and the path
    Dim fname As String, fpath As String
    'this is the workbook of the master
    Dim wb As Workbook
    'this is the sheet to deposit on the master
    Dim ws As Integer
    'this is the last row of the master
    Dim LastRow As Long
    
    
    'this will make everything exicute in the background without showing the user anything that is going on
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
    End With


    'gathers the name of user and the date
    WorkerName = Application.UserName
    DT = Now
    
    'sets the sheet on master that everything is depsoited
    ws = 1
    'name and locatino of master
    fname = "New Microsoft Excel Worksheet.xlsx"
    fpath = "C:\Users\USER\Desktop\"


    'incase of error - ie. if the workbook is not found or already opened
    On Error GoTo ErrorHandler
    Set wb = Application.Workbooks.Open(fpath & "\" & fname)


    'copies user input into their current workbook
    ThisWorkbook.Worksheets(1).Range("A1:H12").Copy
    
    'gatheres last row of master
    LastRow = wb.Worksheets(ws).Cells(wb.Worksheets(ws).Rows.Count, "A").End(xlUp).Row
    
    'adds to last row so you will not overwrite the last data entered
    LastRow = LastRow + 1
    
    'puts in the username and the date of the entry
    wb.Worksheets(ws).Range("A" & LastRow).Value = WorkerName & " - " & DT
    
    'adds another to the last row so you do not overwrite the username and date
    LastRow = LastRow + 1
    
    'paste all values copied from range of source into master
    wb.Worksheets(ws).Range("A" & LastRow).PasteSpecial xlValues
    
    'save and close master
    With wb
        .Save
        .Close
    End With


    'resume screen updating
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
    'everything updated correctly
    MsgBox ("Everything was updated")
    Exit Sub


ErrorHandler:
    
    'error message
    MsgBox ("Something went wrong saving. Please try again later")
    On Error GoTo 0


End Sub

I hope that helps!
~Frab