Embedd Excel Sheet Into Multiple Excel Workbooks in a Folder

I have an Excel workbook open called 'SoureWorkBook'.  There is a sheet in this workbook called 'FormulaSheet', and 'FormulaSheet' is full of formulas solely referencing a sheet called 'DataSheet'.  I need this sheet to be systematically inserted into several other workbooks.

If I have 'SourceWorkBook' opened, is there a VBA code that can be execute to insert this sheet automatically into all of the other ones?

Assume the current location of the current workbook is : C:\My Documents\Folder1\
Assume the current file is called 'SourceWorkBook.xlsm'

Assume the current location of the other workbooks is : C:\My Documents\Folder2\
Assume the target files that need the sheet inserted all start with 'TargetWorkBook - ' and the extensions are 'xlsx'.

Any takers? Any more info needed from me?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Perhaps something like this.
Sub InsertFormulaWS()
Dim wbDest As Workbook
Dim wsFormula As Worksheet
Dim strFileName As String

    Application.ScreenUpdating = False 

    Set wsFormula = Sheets("FormulaSheet")

    strFileName = Dir("C:\My Documents\Folder2\TargetWorkBook*.xlsx")


        Set wbDst = Workbooks.Open("C:\My Documents\Folder2\" & strFileName)
        wsFormula.Copy After:=wbDst.Sheets(wbDst.Sheets.Count)
         wbDst.Close SaveChanges:=True
         strFileName = Dir
    Loop Until Len(strFileName) = 0

    Application.ScreenUpdating = True
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
can I assume that all targets workbooks contains a sheet called 'DataSheet' with the same structure  of the one in SoureWorkBook?
do you need to use only the values(formulas results)  of the FormulaSheet  or you want formulas and apply it against local "DataSheet" of the target workbooks?
ctownsen80Author Commented:
@FarWest -- Hello! yes, all sheets do contain a sheet called "DataSheet" with the same structure. I want the formulas to be applied to each workbook to calculate result respective to that actual workbook-- not values.
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

ctownsen80Author Commented:
@Norie -- Hi and thank you.  I copied and pasted the code into an excel module. I was carefully to manipulate the file locations to match those on my computer, ran the code and "something" happened, but not the anticipated result. There was no error message but it just simple didn't insert the needed tab into the files.

I have attached a couple of examples that can hopefully help.

Assume 'EESample.xlsx" is the current file with the 'FormulaSheet' embedded.
And assume 'EESample_Target.xlsx' is one of several files that need 'FormulaSheet' added via this code.

The file location fo 'EESample.xlsx' is C:\My Documents\Folder1\,
and the file location for the target files is C:\My Documents\Folder2\
NorieAnalyst Assistant Commented:
The code works for me with the attached files and when I use the correct directory for the target workbooks.

There is a slight typo in the code, this,
Dim wbDest As Workbook

Open in new window

should be this.
Dim wbDst As Workbook

Open in new window

However that wouldn't have any affect on things.

Have you tried stepping through the code with F8?
ctownsen80Author Commented:
Got it finally....thanks to you help!  Thank you very much!
I think there is something wrong in EE notification system, I did not notice your answer earlier
but glad that Norie solution worked for you,
good luck
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.