macro/code to insert specific sheet into all workbooks in folder

[prkhan56]

Hello All,
I am using Excel 2013 and I have a Workbook where I have a Sheet name "Notes"
I want to copy this sheet as the last sheet in all the workbooks in a folder in the following path.
"C:\Username\Macro\Test\"

I got the code on this group from the year 2008 but it does not copy the Notes sheet in any of the workbook in the given path.

This is the link to the thread.

macro/code to insert specific sheet into all workbooks in folder

I have a specific Worksheet set up as a formatted "template" and want to add it to every file/workbook in a specific folder. Each of these files only contains Sheet1 and the 'template' is named "add". Is there an easy way to do this?

www.mrexcel.com

I changed xls to xlsx, the sheet name from "add" to "Notes" and the path accordingly but it still does not copy the "Notes" in all the workbook.

Any help in modifying the existing code or a new code would be greatly appreciated.

Note: Both the codes in the above thread does not work

TIA

 

[prkhan56]

Maybe... (Subroutine "test_Modified_w_subfolders" will create copy of sheet Notes in all files .xls in folder Test, include subfolders)

Dim fso As Object, f As Object, f1 As Object, SrcBook As Workbook, fst As Object

Sub test_Modified_w_subfolders()
Dim s As String

s = "C:\Username\Macro\Test\"
s = s & IIf(Right(s, 1) = Application.PathSeparator, "", Application.PathSeparator)

Application.ScreenUpdating = False

Set fst = ThisWorkbook.Worksheets("Notes")
Set fso = CreateObject("Scripting.FileSystemObject")

GetSF s

Application.ScreenUpdating = True

Set SrcBook = Nothing
Set fso = Nothing
Set fst = Nothing
Set f = Nothing

MsgBox "Done!"
End Sub

Private Sub GetSF(path)
Dim SrcBook As Workbook
Set f = fso.GetFolder(path)

    For Each f1 In f.Files
        If Replace(f1.Name, fso.GetBaseName(f1), "") Like ".xls" Then
            Set SrcBook = Workbooks.Open(path & f1.Name)
            fst.Copy After:=SrcBook.Worksheets(SrcBook.Worksheets.Count)
            SrcBook.Close True
        End If
    Next
 
    For Each f In f.SubFolders
        GetSF f.path & Application.PathSeparator
    Next
End Sub

Hello LazyBug,
Your code works but it pops up with and an Exclamation Mark Error saying "Be careful! Parts of your document may include personal information that can't be removed by the Document Inspector." How can we get rid of this please.
Otherwise your code works perfectly.
@Alex thanks for your input. I did change xls to xlsx
Thanks to both of you for all your time and help

 

[LazyBug]

Hello LazyBug,
Your code works but it pops up with and an Exclamation Mark Error saying "Be careful! Parts of your document may include personal information that can't be removed by the Document Inspector." How can we get rid of this please.
Otherwise your code works perfectly.
@Alex thanks for your input. I did change xls to xlsx
Thanks to both of you for all your time and help

Glad we could help & thanks for the feedback.

Go to File > Options > Trust Center > Trust Center Settings > Privacy Options > uncheck the check box "Remove personal information from file properties on save", then OK.

 

[prkhan56]

Glad we could help & thanks for the feedback.

Go to File > Options > Trust Center > Trust Center Settings > Privacy Options > uncheck the check box "Remove personal information from file properties on save", then OK.

Dear LazyBug,
Thanks for your prompt response.
It is unchecked but it still pops up and only when I press Ok then it processes the next file so on and so forth till it displays the MsgBox Done.
Any guess?

 

[LazyBug]

Any guess?

Try add to macro the line

SrcBook.RemovePersonalInformation = False

before

SrcBook.Close True

(I didn't check this.)