Update Variable Data from Multiple Worksheets in a Master Sheet

[Branshine]

Hello!
Long time listener, first time caller.

I have about 500 individual workbooks each with a sheet that is named an individual employee's name, so each sheet has a different name.
Among a lot of other data there are two fields "Changed in Assessed Tier" and "New Assessed Level and Tier"
In most workbooks the data needed is found in C15 and C16, but the row can and does vary in a few. The data will always be in C.
I would like the responses to those two fields (once entered) to populate a masterfile.

This masterfile can be one already generated by supervisor that includes each employee's name and employee ID (so matching fields and returning data)
or if it would be easier this masterfile can be generated by the macro just as long as it includes employee name, employee ID, and supervisor in addition to the two data field responses.

Currently these 500 files are saved in different folders based on supervisor, but can be moved to a central folder, the master sheets are saved in a different location by supervisor. Let me know if the master sheet and all the files need to be in the same location for it to work.
For example: "C:\Users\bwalte5\Desktop\Supervisor Master Sheets- Facilities CP Annual Assessment\Bedell,Susan Marie.xlsx"

I am also open to other solutions, I realize I could just link the files, because they were inherited and not newly created that is a lot of manual work to add the links, but so is opening 500 workbooks to get the info, so any help or advice would be very much appreciated!

 

[mumps]

Assuming that the Master file already exists, would you want to process all 500 workbooks at once or would it be easier to automatically update the Master file as the data in each of the 500 workbooks is entered in the two fields "Changed in Assessed Tier" and "New Assessed Level and Tier"?

 

[Branshine]

I am a Mumps fan! I was hoping you would respond! I actually tried to modify a couple of codes you posted as answers to other questions with no luck!

Ideally it would process in real time, but if it is easier I can run it all at once, because the individual files will be hosted in teams so that the supervisors and managers can complete them.
I can always download them to a folder location in order to run the master sheet populating macro. The "easier" the solution the better so that it can be replicated easily when new sheets are added or taken away.

 

[mumps]

My thinking is that it would be easier and more efficient to run in real time, that is, that the Master would be automatically updated at the time when the data is entered in each individual file. The only draw back is that you would have to be willing to spend a little time to insert a short macro into each of the 500 files or have each team use a few easy to follow steps to insert the macro themselves. This would have to be done only once, saving the file as a macro-enabled file after insertion. The advantage is that data processing would be very fast because it would be done one file at a time as needed instead of processing 500 or more files all at once which would take much more time. If you decide to use this approach, I would need to know if the macro would have to open the existing Master file or if the Master file is already open. If the macro needs to open the Master, I would need the full path to the folder containing the master file. Please advise.

 

[Branshine]

I like the way you think. I think that would work, the people completing the sheets have to open the sheets anyway. The master won't be open if done in real time, so the macro would need to open the master if the master has to be open to add data to it. Here is the path "C:\Users\bwalte5\Desktop\Facilities Career Progression Master Sheets.xlsx"

 

[mumps]

This is the macro that would have to be copy/pasted into the worksheet code module of each of the individual workbooks by doing the following: right click the tab name for the sheet containing the data to be copied and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. This would have to be done only once. Re-save the file as a macro-enabled file. You will now have two copies of the file, one with an "xlsx" extension and one with an "xlsm" extension. To avoid confusion, you can delete the file with the "xlsx" extension because it is simply a duplicate without the macro. The macro also assumes that the Master file has only one sheet. If the are more than one sheet, you will have to replace the "1" in the code (in red) with the destination sheet name. Enter a value in column C and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim fnd As Range, ID As Range, desWB As Workbook, srcWS As Worksheet
    Set srcWS = ActiveSheet
    Set fnd = Range("A:A").Find("Change in Assessed Level and Tier")
    If Intersect(Target, Range("C" & fnd.Row & ":C" & fnd.Row + 1)) Is Nothing Then E
    Application.ScreenUpdating = False
    Set desWB = Workbooks.Open("C:\Users\bwalte5\Desktop\Facilities Career Progression Master Sheets.xlsx")
    Set ID = Sheets(1).Range("A:A").Find(srcWS.Range("C3").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not ID Is Nothing Then
        Select Case Target.Row
            Case Is = fnd.Row
                ID.Offset(, 6) = Right(Target, 1)
            Case Is = fnd.Row + 1
                ID.Offset(, 5) = Target
        End Select
    End If
    desWB.Close True
    Application.ScreenUpdating = True
End Sub

 

[Branshine]

Hi! It gave a Compile Error saying Sub or Function not defined? Any advice?

 

[mumps]

It is difficult to say what may have caused the error because I don't have access to your files. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshots (not pictures) of one of the individual sheets and your Master sheet. Alternately, you could upload a copies of the two files (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[Branshine]

It is difficult to say what may have caused the error because I don't have access to your files. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshots (not pictures) of one of the individual sheets and your Master sheet. Alternately, you could upload a copies of the two files (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Facilities Career Progression Master Sheets.xlsx | Powered by Box

app.box.com

Bedell,S_Alexander,-KA1000.xlsm | Powered by Box

app.box.com

 

[mumps]

The error you received was due to some sloppiness on my part. However, they are a couple of other issues which would have caused problems. The ID in the individual sheet (C3) starts with a zero but the ID's in column A of the Master don't include that initial zero so the two ID's don't match. The text "Change in Assessed Level and Tier " (A15) has a trailing space in it which would also have created a matching problem. One of the destination columns is different from that in your original post. The revised code below has taken these issues into account. Give it a try.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim fnd As Range, ID As Range, desWB As Workbook, srcWS As Worksheet
    Set srcWS = ActiveSheet
    Set fnd = Range("A:A").Find("Change in Assessed Level and Tier", LookIn:=xlValues, lookat:=xlPart)
    If Intersect(Target, Range("C" & fnd.Row & ":C" & fnd.Row + 1)) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set desWB = Workbooks.Open("C:\Users\bwalte5\Desktop\Facilities Career Progression Master Sheets.xlsx")
    Set ID = Sheets(1).Range("A:A").Find(Mid(srcWS.Range("C3").Value, 2), LookIn:=xlValues, lookat:=xlWhole)
    If Not ID Is Nothing Then
        Select Case Target.Row
            Case Is = fnd.Row
                ID.Offset(, 6) = Left(Target, 1)
            Case Is = fnd.Row + 1
                ID.Offset(, 7) = Target
        End Select
    End If
    desWB.Close True
    Application.ScreenUpdating = True
End Sub