Update external workbooks with changes made in master workbook

Update external workbooks with changes made in master workbook

I have browsed the forum and could not find an answer to what I was looking for. My company wants me to have a Master Spreadsheet that when changed will update the corresponding areas in in Spreadsheets 1, 2, 3, 4, 5, 6 and so on and so forth (including their tabs). There are about 50 spreadsheets in all. A student helper found some code that she thought may help, but it doesn't work in the way that I need it to(updates all cells and all tabs.) It only updates cell A1 in the spreadsheets, and it automatically opens them all before it does that (which takes about 4 clicks for each spreadsheet by the user.) I would like a code that updates specific ranges of cells and does not need to open all 50 spread sheets to do so. If anyone needs more information please let me know. I tried to be as specific as I could. I'll post the code below, and THANK YOU for any help that anyone could offer.

    Sub Example()
        Dim MyPath As String, FilesInPath As String
        Dim MyFiles() As String, Fnum As Long
        Dim mybook As Workbook
        Dim CalcMode As Long
        Dim sh As Worksheet
        Dim ErrorYes As Boolean

        'Fill in the path\folder where the files are
        MyPath = "C:\Users\Ron\test"

        'Add a slash at the end if the user forget it
        If Right(MyPath, 1) <> "\" Then
            MyPath = MyPath & "\"
        End If

        'If there are no Excel files in the folder exit the sub
        FilesInPath = Dir(MyPath & "*.xl*")
        If FilesInPath = "" Then
            MsgBox "No files found"
            Exit Sub
        End If

        'Fill the array(myFiles)with the list of Excel files in the folder
        Fnum = 0
        Do While FilesInPath <> ""
            Fnum = Fnum + 1
            ReDim Preserve MyFiles(1 To Fnum)
            MyFiles(Fnum) = FilesInPath
            FilesInPath = Dir()
        Loop

        'Change ScreenUpdating, Calculation and EnableEvents
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .EnableEvents = False
        End With

        'Loop through all files in the array(myFiles)
        If Fnum > 0 Then
            For Fnum = LBound(MyFiles) To UBound(MyFiles)
                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
                On Error GoTo 0

                If Not mybook Is Nothing Then


                    'Change cell value(s) in one worksheet in mybook
                    On Error Resume Next
                    With mybook.Worksheets(1)
                        If .ProtectContents = False Then
                            .Range("A1").Value = "My New Header"
                        Else
                            ErrorYes = True
                        End If
                    End With


                    If Err.Number > 0 Then
                        ErrorYes = True
                        Err.Clear
                        'Close mybook without saving
                        mybook.Close savechanges:=False
                    Else
                        'Save and close mybook
                        mybook.Close savechanges:=True
                    End If
                    On Error GoTo 0
                Else
                    'Not possible to open the workbook
                    ErrorYes = True
                End If

            Next Fnum
        End If

        If ErrorYes = True Then
            MsgBox "There are problems in one or more files, possible problem:" _
                 & vbNewLine & "protected workbook/sheet or a sheet/range that not exist"
        End If

        'Restore ScreenUpdating, Calculation and EnableEvents
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = CalcMode
        End With
        End Sub

See my post on Ozgrid :http://www.ozgrid.com/forum/showthread.php?t=148438

THANK YOU