PDA

View Full Version : Update the Master Workbook from the Copy.



DragonWood
04-12-2018, 12:52 PM
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.

Logit
04-12-2018, 04:39 PM
.
Just one of numerous examples on the Net :

https://www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/

SamT
04-12-2018, 07:10 PM
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