Consulting

Results 1 to 3 of 3

Thread: Update the Master Workbook from the Copy.

  1. #1
    VBAX Regular
    Joined
    Nov 2011
    Location
    Houston, TX
    Posts
    27
    Location

    Question Update the Master Workbook from the Copy.

    Greetings,


    I have a master workbook that is always in the same location and always has the same name. "C:\Hexagon\Hexagon Service Engineer Assistant\Hexagon Service Engineer Assistant.xlsm"


    Within this workbook is a page called "Time & Mileage". Only Columns A through H can be edited by the user. The rest of the columns on the page are calculated.


    When the user first opens the Master workbook, there is a form that pops up, so they can enter some of the default information needed for their region.


    Once they've filled in the information needed, and save the workbook, it leaves the Master blank and creates a new set of folders based on the information they entered. Then places a copy of the entire workbook, with a new name in that folder.


    For all other data in the workbook, this is ideal. However, the Time & Mileage page is supposed to be tracking the travel and work hours over the entire year, across multiple jobs.


    So, what I need to be able to do is update the Time & Mileage page of the Master workbook from whatever renamed copy the user is actively using.


    Right now, the user just types the information directly into the cells on the Time & Mileage page. I am considering creating a form they will use to enter this information, so that could be the activator for the code to update the master. Or I'll just add a button they can do it with. The trigger isn't the problem though. The problem is how do I make it update the Master?


    The form that opens at the beginning does open in Modeless mode, so the workbook can still be used, even though the form is open.


    Thanks.
    Dragon

    "You don't need to take a person's advice to make them feel good; just ask for it." ~ Laurence J. Peter

  2. #2
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    Just one of numerous examples on the Net :

    https://www.exceltrainingvideos.com/...automatically/

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    So, what I need to be able to do is update the Time & Mileage page of the Master workbook from whatever renamed copy the user is actively using.
    Just use the same UserForm they enter all the other info on.

    Const TimeStartColumn = 1
    Const TimeEndColumn - 2
    Const MilageStartColumn = 3
    Const MilageEndColumn = 4
    
    Private Sub UpdateTimeAndMilageInMasterBook()
    Dim LR as Long
    
    With Workbooks("Hexagon Service Engineer Assistant").Sheets("TimeAndMilage")
       LR = .Cells(Rows.Count, TimeStartColumn).End(xlUp).Row + 1
       .Cells(LR, TimeStartColumn) = TextBoxStartTime
       .Cells(LR, TimeEndColumn) = TextBoxEndTime
       .Cells(LR, MilageStartColumn) = TextBoxStartMilage
       .Cells(LR, MilageEndColumn) = TextBoxEndMilage
    End With
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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