Open workbooks and paste in value

[VBANovice46]

Hi All,

I'm currently trying to write some code that does the following: (a) opens and unprotects workbooks (the names of which are listed), (b) copies and pastes a value into a specific cell on each sheet of the open workbooks, (c) saves, protects and closes the workbooks that have been opened.
I'm not sure how best to go about this, and have an example to try and illustrate what I'm trying to achieve. I would like to write code that passes the workbook names through and carries out the tasks above. Would it be a good idea to put the workbook names in an array?

Any advice would be greatly appreciated.TestData(111115).xlsm

[Mumps1]

What is the path of the folder that contains the workbooks listed in D2:D4?

[VBANovice46]

Thanks for the reply Mumps1
Currently they're in My Documents whilst I'm building the code, but eventually they'll be on a sharepoint site and therefore will need to be checked in and out (I don't yet have the sharepoint file path); the current file path is as follows: H:\Documents\Accounts\ReportingData
Hope that helps...

[Mumps1]

This macro will loop through all the files in your folder ("H:\Documents\Accounts\ReportingData\") so make sure that the folder contains only the workbooks to which you want to copy the data. Eventually, you will have to change the folder path.

Sub LoopThroughFolders()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim wbSource As Workbook
    Set wbSource = ThisWorkbook
    Dim wbOpen As Workbook
    Const strPath As String = "H:\Documents\Accounts\ReportingData\"
    ChDir strPath
    strExtension = Dir("*.xls*")
    Do While strExtension <> ""
        Set wbOpen = Workbooks.Open(strPath & strExtension)
        If wbOpen.Name = "Workbook A.xlsx" Then
            With wbOpen
                .Unprotect
                For Each ws In .Sheets
                    ws.Range("Q2") = wbSource.Sheets("Control Sheet").Range("B1")
                Next ws
                .Protect
                .Close True
            End With
            strExtension = Dir
        Else
            With wbOpen
                .Unprotect
                For Each ws In .Sheets
                    If ws.Name <> "Summary" Then
                        ws.Range("Q2") = wbSource.Sheets("Control Sheet").Range("B1")
                    End If
                Next ws
                .Protect
                .Close True
            End With
            strExtension = Dir
        End If
    Loop
    Application.ScreenUpdating = True
End Sub

[VBANovice46]

Many thanks for this Mumps1 - this is super helpful; I'm currently trying to split my code into different sections, I am going to try and have a sub function which checks out, opens and unprotects each of the listed workbooks, and a sub routine that saves, protects, closes and checks in the listed workbooks so that they can be called elsewhere in the code when needed.
The check out, open and unprotect code I've attempted is below, does this look correct to you? I've defined the file paths and file names in the main body of the code and passed them to the sub routine:

Sub CheckOutOpen(strFullDesc1 As String, strFullDesc2 As String, strFullDesc3 As String)
If Application.Workbooks.CanCheckOut(strFullDesc1, strFullDesc2, strFullDesc3) = True Then
    Application.Workbooks.CheckOut strFullDesc1
    Application.Workbooks.Open strFullDesc1
    '.Unprotect workbook 1 here
    Application.Workbooks.CheckOut strFullDesc2
    Application.Workbooks.Open strFullDesc2
    '.Unprotect workbook 2 here
    Application.Workbooks.CheckOut strFullDesc3
    Application.Workbooks.Open strFullDesc3
    '.Unprotect workbook 3 here
End If

Thanks again...

[Mumps1]

My apologies, but I'm not familiar with the 'Checkout' function.