excel loop through folder sheet copy and replace formula link to master file

  • Dear Sirs....I would appreciate if you can help me with my little project for our school

    I have a master sheet in which I have a tab which has some calculations and references, which I want to copy to all workbooks in a particular folder and update the references (links) to that sheet's values.

    for example - my master workbook's name is Master.xlsm - I have a folder in c:\test\ which contains 100s of files.... I want a tab named "DFG" (which has calculations and take's value from "main" tab in master.xlsm to copied to all files in that folder, but each of those file's DFG tab should be taking value's from it's own "main tab" - for eg: March_20_2019.xlsx should be taking values (references) from that file and not from "Master.xlsm...

    I have got the copy portion up and running with this code..

    for now, I have the replace code running as a separate macro and I have to run it for each file separately....

    Sub Macro3()
    ' Macro3 Macro
    Cells.Replace What:="='[master.xlsm]main'", Replacement:="='main'!", _        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _        False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    End Sub

    is there a way to achieve both of this in one shot by incorporating the replace option in the first working code itself? any help would be appreciated.


    Lisa McRandall

  • Hello and Welcome to the Forum :)

    The short answer to your question is ... obviously ... Yes ;)

    The even shorter answer could be to insert the instruction Application.Run("Macro3") in your first macro ...

    Hope this will help


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thank you Carim - that is working - wonderful!

    one last thing - currently i have a fixed folder. could you please help modiy the code to pick a folder on run?

    i played with set f = Application.FileDialog(msoFileDialogFolderPicker) but not working :(

    sorry i don't have programming skills - chemistry teacher this end.



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!