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.Close True
- 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....
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.