Hello all,
I would like to run a for loop that inserts 2 worksheets ("Projects" and "Collated") into a folder's worth of other workbooks.
I have Set the worksheets that I wish to insert but ensure on how to add them to each of the workbooks.
I would like to run a for loop that inserts 2 worksheets ("Projects" and "Collated") into a folder's worth of other workbooks.
I have Set the worksheets that I wish to insert but ensure on how to add them to each of the workbooks.
VBA Code:
Sub add_sheets_to_health_assessments()
'Macro Purpose:
'For each health assessment
'open the health assessment,
'add worksheets,
'save workbook,
'close workbook,
Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim oFolderFiles As Object
Dim sFilePath As String
Dim ArchiveReportingWB_name As String
Dim ProjectsWS As Worksheet
Dim CollatedWS As Worksheet
sFilePath = Application.ActiveWorkbook.Path
ArchiveReportingWB_name = "Health Assessment Archive Reporting"
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(sFilePath)
'Macro Performance Optimisation Actions
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.EnableEvents = False
secAutomation = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Workbooks.Open (sFilePath & "\" & ArchiveReportingWB_name & ".xlsm")
Windows(ArchiveReportingWB_name & ".xlsm").Activate
Sheets("Projects").Select
Set ProjectsWS = Workbooks(ArchiveReportingWB_name).Sheets("Projects")
Sheets("Collated").Select
Set CollatedWS = Workbooks(ArchiveReportingWB_name).Sheets("Collated")
For Each oFile In oFolder.Files
If InStr(1, oFile.Name, "TSP") > 0 And InStr(1, oFile.Name, "Blank Template") = 0 Then
Workbooks.Open (oFile)
Windows(oFile.Name).Activate
wSheet.Name = "Iterations"
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
Next oFile
End Sub