Hi all,
I am working on a project where I need to transfer multiple sheets from one standardized workbook to multiple files within a folder. This will continue throughout sub-folders within the folder I've selected.
I've managed to get this far but I am not sure how to fix the last bit. Not sure how to move/copy multiple sheets onto active file that is open. Tried to use activeworkbook but that doesn't work...
Is there any way to fix this code slightly to make it work?
p.s. I want all the sheets pasting to be after sheet "First".
End With
End Sub
I am working on a project where I need to transfer multiple sheets from one standardized workbook to multiple files within a folder. This will continue throughout sub-folders within the folder I've selected.
I've managed to get this far but I am not sure how to fix the last bit. Not sure how to move/copy multiple sheets onto active file that is open. Tried to use activeworkbook but that doesn't work...
Is there any way to fix this code slightly to make it work?
p.s. I want all the sheets pasting to be after sheet "First".
VBA Code:
Private Sub Open_Workbooks_In_Folder(folderPath As String, matchWorkbooks As String)
Static FSO As Object
Dim thisFolder As Object, subfolder As Object
Dim thisFile As Object
Dim wb As Workbook
If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
Set thisFolder = FSO.GetFolder(folderPath)
Application.DisplayAlerts = False
For Each thisFile In thisFolder.Files
If LCase(thisFile.Name) Like LCase(matchWorkbooks) Then
Workbooks(1).Activate
Sheets(Array("A", "B", _
"C", "D", _
"E", "F", "G", _
"H", "I")).Select
Workbooks(2).Activate
Sheets(Array("A", "B", _
"C", "D", _
"E", "F", "G", _
"H", "I")).Copy After:=ActiveWorkbook.Sheets("First")
wb.Close SaveChanges:=True
DoEvents
End If
Next
Application.DisplayAlerts = True
'Do subfolders
For Each subfolder In thisFolder.SubFolders
Open_Workbooks_In_Folder subfolder.Path, matchWorkbooks
Next
End Sub
Public Sub Open_All_Workbooks_In_Folders()
Dim FldrPicker As FileDialog
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Open_Workbooks_In_Folder .SelectedItems(1), "*.xlsx"
Application.ScreenUpdating = False
Application.EnableEvents = False
MsgBox "Done"
End If
End Sub