Copy formula to multiple workbooks

[egobrain]

Hi there,

I need your help guys. I have hundred of excel workbooks and want to add extra data to each one. For example, I need to write lookup formula in one cell to retrieve data from one source file and then copy this formula to the same cell in every excel file placed in same folder. I know VBA will be necessary for this task but didn't find any solutions with google. Could you help me out to avoid this routine?

 

[mumps]

In which sheet and range is the data you want to retrieve in the source file? Into which cell and sheet do you want to copy/paste the source data? What is the full path to the folder containing your destination files? What is the extension of those files (xlsx, xlsm)? Are they the only files in that folder?

 

[egobrain]

Well here is the situation: I have hundreds of excel files containing data about each project. In workbook has only one sheet. Unique project number is placed, let's say, in B2 cell in every workbook. There is another workbook where these unique numbers are listed in one column and the names of the project managers in another column. All these workbooks are place in the same folder and each one has unique name. I want to write simple lookup formula to get project manager's name in C3 cell for each workbook. Extension of these files are xlsx.

 

[mumps]

Let's assume that the unique numbers are in column A and the names of the project managers are in column B. Do you want to find the unique number in B2 in every workbook in column A and return the corresponding project manager's name in C3 of every workbook? What is the full path to the folder?

 

[egobrain]

Exactly. I want to add each project file the project manager's name. If it was 10 or 20 workbooks then it would be possible to open each workbook and write simple vlookup, but there are too many projects so its practically impossible to do it manually, at least I can't do that.
Honestly, I don't understand why does the full path of folder matter?

 

[mumps]

I don't understand why does the full path of folder matter?

The macro needs to know where the files are located so they can be updated.

 

[egobrain]

ok, understand. But I don't think it's a big deal. If there were a marco I would change the path

 

[mumps]

Try this macro. Change the folder path (in red) to suit your needs.

Sub GetName()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWB As Workbook, fnd As Range
    Set srcWS = ThisWorkbook.Sheets(1)
    Const strPath As String = "C:\Test\" 'change folder path to suit your needs
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set desWB = Workbooks.Open(strPath & strExtension)
        Set fnd = srcWS.Range("A:A").Find(Sheets(1).Range("B2").Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            Sheets(1).Range("C3") = fnd.Offset(, 1)
        End If
        desWB.Close True
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub