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..
Sub InserTAB() Dim SrcBook As Workbook Dim fso As Object Dim f As Object Dim ff As Object Dim f1 As Object Dim fst As Object Application.ScreenUpdating = False Set fst = ThisWorkbook.Worksheets("DFG") Set fso = CreateObject("Scripting.FileSystemObject") Set f = fso.Getfolder("C:\test\") Set ff = f.Files For Each f1 In ff Set SrcBook = Workbooks.Open(f1) fst.Copy After:=SrcBook.Worksheets(1) SrcBook.Worksheets(1).Activate SrcBook.Close True Next Application.ScreenUpdating = True Set SrcBook = Nothing Set fst = Nothing Set fso = Nothing Set f = Nothing Set ff = Nothing End Sub
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.