Results 1 to 3 of 3

Thread: Link multiple workbooks to one master workbook

  1. #1
    Neophyte amsterp's Avatar
    Join Date
    Feb 2019
    Posts
    1
    Articles
    0
    Excel Version
    2010

    Link multiple workbooks to one master workbook



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello.

    We are a construction company and are looking for a specific set up. I've shared my folder on dropbox and will reference the files that I have shared.

    Our estimators use a sheet called "AAA Blank Job Sheet" when working up the estimate. They leave the "Job No." field blank and save it as the job name. We want to keep each job as its own file within the folder in case changes need to be made.

    Now lets say the job was awarded to us. The accounting department gives it a job name. Once the job name is assigned I would like to open the job sheet and enter that newly assigned number in the "Job No." cell, then have all the information from that job sheet exported to a blank row in the "AAA job List.xlsm" workbook.

    This way each actual hanging file in the file drawers of our office will have a copy of that jobs specific "Job Sheet" inside. Then the "AAA Job List" file can be distributed to the guys in the field and the plant so they know the pertinent information of each job.

    Thanks!!!


    https://www.dropbox.com/sh/087jsg8wh...-hX0cGeTa?dl=0

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    768
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Hello amsterp,

    couple of questions:

    why are "AUCH NORTHVILLE HIGH SCHOOL" and "AUCH SASHABAW MIDDLE SCHOOL" files
    .xlsx and "Picanos" is .xlsm ?
    I would think they should be all .xlsx

    why do the job numbers in B5 include quotation marks when they don't in the Job List file ?
    reason I ask is when searching the job list to see if that job number already exists... they don't match.

    Consistency is best practice.

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    768
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Here's a little macro you could put in a standard module in the "AAA Blank Job Sheet.xlsm" workbook and use to save
    the new "job name" workbooks as .xlsx in the same folder as the blank.
    Code:
    Sub SaveJobSheet()
        
        Dim newFname As String, fPath As String
        
        'name for new workbook
        If Range("B5") <> "" Then
            newFname = Range("B5")
        Else
            MsgBox "No job name in B5"
            Exit Sub
        End If
        'folder to save into
        fPath = ThisWorkbook.Path
        'create new workbook of sheet
        ActiveSheet.Copy
        'name and save new workbook
        ActiveWorkbook.SaveAs Filename:=fPath & "\" & newFname, FileFormat:=51
    
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •