Update a Master Sheet with Changed or New Data From Another Sheet

[idelta]

If anyone has the time, I would greatly appreciate the help with this macro.

I am trying to find a VBA method of updating a "master" sheet with new, or changed data based on another sheet.

Background (I can't upload the workbook due to proprietary information):
I have a "master" task list that I keep track of past, current, and future tasks. Each week I receive a MS Project output from our scheduling/tasking department. This output only contains current and future tasks, not old tasks. Tasks can be updated (like completion dates), or new tasks can be added. New tasks can be mixed within the output, not always at the bottom of the output.

The "master" list contains past tasks as I need to keep historical data for charging purposes. So, row data will not align between the two sheets. Additionally, the "master" list has added columns as my leads provide information as who is responsible for the task, etc.. I need to keep this data.

Between the two sheets, columns A-U are the same. In the "master", columns V-AC are the added columns. Column A in both sheets contain the unique identifier code.

The "master" sheet is titled "TMS Tasks", and the output sheet is titled "Updated TMS"


I found a VBA example of what I am trying to accomplish from a 2008 post by user Smitty
http://www.mrexcel.com/forum/excel-questions/316995-refresh-master-sheet-reflect-changes-other-sheets.html
and modified it to the limit of my VBA experience, but need more experienced help to modify it further for what I would like to do.

The code I found and have modifed to this point:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rng As Range, LstRw As Range, TMSrng As Range, c As Range
Dim TMS As String, FirstAddress As String, CurrentSheet As String
Dim ws As Worksheet

Set rng = Target.Parent.Range("A:A")
If Target.Count > 1 The Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target.Parent.Name <> "TMS Tasks" Then

CurrentSheet = ActiveSheet.Name
TMS = Cells(Target.Row, "A").Text
Sheets("TMS Tasks").Activate
With Sheets("TMS Tasks").Range("A1:A65500")
Set c = .Find(TMS, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Target.EntireRow.Copy Sheets("TMS Tasks").Range("A" & c.Row)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
Else
Set LstRw = Sheets("TMS Tasks").Cells(Rows.Count, "A").End(xlUp)
Target.EntireRow.Copy LstRw.Offset(1)
End If
End With
End If
End Sub

I am looking for some guidance on the following:

>I would like to move this code from a Workbook change event into a module as I don't need it to automatically update whenever I, or someone else is working in the file.

>The code works up to

Target.EntireRow.Copy LstRw.Offset(1)

then I get an error stating "Copy Method of Range Class Failed". I haven't been able to find a solution to this... mainly as I don't completely understand the Target expression.

>How do I specify to only copy over columns A-U instead of the entire row so I do not erase user input data in the proceeding columns?


I would greatly appreciate any time someone has to offer to help out. Please let me know if any additional informaton is needed.

 

[JLGWhiz]

In the Workbook_SheetChange event you will see the statement 'ByVal Target As Range'. That statement means that the cell you change is automatically the Target and wherever Target appears in the code, it is referring to that cell as a range object. i.e. if you change cell B5, then vba sees Target as Range("B5") for whichever sheet is active when the change is made.

If you only want to copy cells A:U of the Target row, then

Cells(Target.Row, 1).Resize(1, 21).Copy Sheets(TMS Tasks").Cells(Rows.Count, 1).End(xlUp)(2)
[/Code)

Will copy A:U of the target row to the next available row on the TMS Tasks sheet.

If you move the macro to the standard code module, you will need to change the title line to something other than the one for the event code, and remove the ByVal statements from the parentheses.  The Target variable in the code will need to be replaced with a different variable name of your choosing and declared with a Dim statement as opposed to the ByVal method.

 

[L. Howard]

Try something like this.

Here, I get the value sought after from cell F1-- FndThis = Range("F1"),. You could also use anIinputbox to set FndThis to the value being looked up.

If you really don't need the ENTIRE row, you can use the Resize line to capture the number of columns desired.

In my test, I am searching for FndThis on Sheet1 and copy to TMS Tasks sheet.

Howard

Sub aFind_Code_1()

Dim FndThis As String
FndThis = Range("F1")
Dim SearchRng As Range
Dim LstRw As Range

Set SearchRng = Sheets("Sheet1").Range("A:A").Find(What:=FndThis, _
                                               LookIn:=xlValues, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False)
                                               
If Not SearchRng Is Nothing Then
   Set LstRw = Sheets("TMS Tasks").Cells(Rows.Count, "A").End(xlUp)(2)
   SearchRng.EntireRow.Copy LstRw
   'SearchRng.Resize(1, 12).Copy LstRw
 Else
   MsgBox "No match found. "
End If
End Sub

 

[idelta]

L. Howard - Thank you for the suggestion. Unfortunately, if I understand it correctly, I need the find parameter to be more dynamic. Basically, to loop through each unique identifier. Even with including an user input box may not work well as I don't always know which line changed. However, I do like how you handled the copy of the row if not found using SearchRng. I have another macro I pieced together that I could replace with your method.

 

[idelta]

JLGWhiz - Thank you for taking the time to respond and explaining the Target expression. The code is now a little more clearer. The copy modification you recommended was a great suggestion and worked. I did find another method with some research after I posted my question, but your suggestion I think worked a little cleaner than what I put together.

However, I realized after working with this that I need to manually change fields in the output for this code to replace the data in the master sheet. Unfortunately, I don't always know what changed, especially with start/stop dates (the dates are calculated based on dependencies in Project). Basically, I copy in the Project output into the workbook.

So, I am kind of back to the beginning. Now I need to figure out a way to compare the output (Updated TMS) to the master (TMS Tasks) based on the UIC in column A between the two sheets. If a difference is found, copy that row (columns A-U) over the one in the master. If it's new, add that to the bottom of the master.

If I didn't need to keep the old tasks in the master, I could use Index/Match based on UIC and Column Header and just auto-fill the formula down, then figure out how to handle the new tasks.

If you, or anyone else is available to do so, can provide any suggestions or guidance I would again appreciate the help. Until then, I'll do some more searching and see if I can come up with something.

 

[idelta]

I searched around and found several code examples of what I am trying to do, but not exact. But I took bits and pieces and made an attempt to write the macro myself (I would rather try and learn, rather than have it done for me). However, it didn't go too well as I am running into a few errors.

To summarize my intent:

• Compare an Update worksheet to a Master worksheet using an unique ID (UID) in column A between the two sheets
• If an Update UID is not found in the Master, add it to the end of the Master Worksheet
• If an Update UID is found, copy columns A:U of Update to the matching UID row in the Master Worksheet
• I only want to compare Update to Master and make changes based on Update. I have old tasks in Master that I need to keep.

My attempted code:

Sub UpdateTasks()
Dim Master As Worksheet, Update As Worksheet
Dim LstRw As Range
Dim r As Long
Dim f As Range
Dim TMS As String
Dim TMSr As Range

Set Master = Sheets("TMS Tasks") 'Master Sheet
Set Update = Sheets("Update") 'Output From Tasking
r = 2 'Starting Row in Update, 1 if no column headers
TMS = Update.Range("A" & r).Value 'UID to match in TMS Tasks
Set TMSr = Update.Range("A" & r)

    Do While Not TMS = ""
        Set f = Master.Columns("A:A").Find(what:=TMS, LookIn:=xlValues, lookAt:=xlWhole)
        If f Is Nothing Then 'Copy new task to end of Master
            Set LstRw = Master.Cells(Rows.Count, "A").End(xlUp)
            TMSr.EntireRow.Copy Master.Cells(Rows.Count, 1).End(xlUp)
        Else 'Replace existing task in Master with values from Update (replaces column A:U of the row)
            TMSr.Range("A" & r & ":U" & r).Copy Master.Range("A" & f.Row)
        End If
        r = r + 1
        TMS = Update.Range("A" & r).Value
    Loop
End Sub

What is actually happening:

• Pasting a blank cells over the matched UID in Master, or
• Adding the matched UID data to the end of Master
• Doesn't seem to loop
• Getting a "Copy Method of Range Class Failed" at this line:

TMSr.EntireRow.Copy Master.Cells(Rows.Count, 1).End(xlUp)

I am relatively new to VBA in that I struggle with writing a macro that I haven't had exposure to before. I would greatly appreciate any advice or help someone could give.

I know my code may be a little chopped so if there is a better way of doing this, please let me know. I would love to expand my knowledge and experience with VBA.

Thank you much in advance,

Cheers!

 

[L. Howard]

If you can, how about posting a link to an example workbook. You can use one of the link utilities, I use Drop Box, but there are others.

Re state what you want to happen and where. I would take a look and see what I could do.

Howard

 

[idelta]

Awesome! Thanks for offering to help out.

Basically, my intent in summary is

• Compare an Update worksheet to a Master worksheet (both in the same workbook) using an unique ID (UID) in column A between the two sheets
• If an Update UID is not found in the Master, add it to the end of the Master Worksheet
• If an Update UID is found, copy columns A:U of Update to the matching UID row in the Master Worksheet. Columns V:AB in the Master are custom columns that I use, so these can't be overwritten (i.e. copy/paste the while row)
• I only want to compare Update to Master and make changes based on Update. I have old tasks in Master that I need to keep.

I've made a few attempts at writing this, with both examples in this thread. The first example (first post of thread) was using a worksheet change event, but I then realized this would not be optimal as I would have to manually affect the fields that changed which I don't always know (it's an output sent out by our scheduling department). So my second attempt (sixth post of the thread) was moving it to a normal module. I thought I was close but ended up against some errors which are detailed in that post.

I created a mock worbook as I can't attach the actual workbook. But it is the same in look and feel.

https://drive.google.com/file/d/0B-LO4lCt6FMISm5tNjVSMXZoWXc/view?usp=sharing

Please let me know if you need any additional information. And again, thanks for the assistance.

 

[L. Howard]

So regarding the downloaded workbook...

1. If a UID on the sheet Output (column A) IS found on the sheet TM Tasks (column A), then over write that row on TM Tasks with the data from Output columns A to U.

2. If a UID IS NOT found the sheet Output (column A) then copy that un-found row (column A to U) from Output to the next blank row UNDER the data in TM Tasks.

3. The shaded area, columns V to AD are to be untouched/unaltered and are not a part of this exercise.

Your narrative in the last post is a bit confusing, in that you deal with Update and Master sheets while the download workbook is sheets Output and TM Tasks.

•I only want to compare Update to Master and make changes based on Update.

I assume if I have the scenario of 1, 2, 3 (above) correct, then we are on track?

Howard

 

[idelta]

Your assumptions are correct!

And I apologize for the confusion with the worksheet names. I named the sheets in the code Update and Master to help keep track of the sheet purposes. But Update = Output, and Master = TMS Tasks. In my head it worked out