Link multiple workbooks to one master workbook

amsterp

New member
Joined
Feb 12, 2019
Messages
1
Reaction score
0
Points
0
Excel Version(s)
2010
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/087jsg8wh4p21la/AAA7nTwXLBDZuTZa-hX0cGeTa?dl=0
 
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.
 
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
 
Back
Top