excel macro to copy sheet and also replace formula reference

mcrandall99

New Member
Joined
Apr 13, 2021
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello,

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

VBA 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....

VBA Code:
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


how do I achieve both of this in one shot by incorporating the replace option in the first working code itself? any help would be appreciated.

thanks
Randall
 
Last edited by a moderator:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Hello mcrandall99 - Welcome to the forum. You might try something like the code below which calls the second macro before the first is completed. Just before ending the first macro I added a Call Macro3 which might do what you need. Hope this helps get you started.

VBA 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

'New code added
Call Macro3

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top