Copy/Paste Between Workbooks

[Peteor]

Ok, I am at the very first stages of my macro, and am kinda stuck with the logic/Syntax. I will be scaling this up to be much larger. I am wondering how to accomplish the following:

File_Path = "Desktop/Sandbox" 'This folder will have several sub-folders'

If (A file-name in File_Path contains text "Example") Then 'If the string exists, even if it is part of another string. All files will be Excel files.'

Open the workbook 'I will refer to this as workbook A.'
Copy Rows 6 to the end of the data
Activate.Workbook ("Master List") 'This file is stored in another folder all-together.'
Paste information into the Active workbook
Iterate this loop for all instances of "Example" 'When new information is pasted, paste under last used row. I don't want to overwrite the previous iteration'

Else
End If

 

[Peteor]

Also, I just noticed there could be any number of unused rows between my rows with data. Not optimal I know, but I cannot change my source files.

 

[Peteor]

I just added my 4 favorite people on this forum =).... Would anyone be able to assist?

 

[bsquad]

Hi Peteror,

I might be able to assist (sometime this week or next); I just created something like this to take pdfs with a wildcarded name and dump them in a main folder.
As a side note, It may be best to remove the empty rows on your master after the aggregation - depending on what other code you have after it

 

[bsquad]

Peteor,

Run the FindFile() only, it calls the DoFolder
-you'll pick your Sandbox folder from the dialog box that it pops up and it will search through any or all layers of its subfolders
you'll also have to adjust the wildcard "*Example*" as it will not pickup where the file name Filenameexample.xlsx, it will only pickup on FileName example.xlsx. This is also not filtered on excel only files, so if you have word docs or something it may error out.
This is also assuming everything is on the first worksheet of the workbooks. - so you can adjust accordingly

Option Private Module
Option Explicit
Dim FileSystem As Object
Dim HostFolder As String
'Dim ResidentFolder As String
Dim y As Long


Sub FindFile()
    Dim SourceFolder As FileDialog
    Dim DestinationFolder As FileDialog
    Dim sItem As String
    Dim sItem2 As String
Application.ScreenUpdating = False


    Set SourceFolder = Application.FileDialog(msoFileDialogFolderPicker)
        With SourceFolder: .Title = "Select Source Folder": .AllowMultiSelect = False: .InitialFileName = Application.ActiveWorkbook.FullName
            If .Show <> -1 Then GoTo NextCode
            sItem = .SelectedItems(1)
        End With
            HostFolder = SourceFolder.SelectedItems(1)
                Set FileSystem = CreateObject("Scripting.FileSystemObject")
        DoFolder FileSystem.GetFolder(HostFolder)
        MsgBox ("done")
        Application.StatusBar = False: Application.ScreenUpdating = True
NextCode:
    Set SourceFolder = Nothing
End Sub
Sub DoFolder(Folder)


    Dim z, x, y As Long
    Dim SubFolder
    Dim iWorkbook, eWorkbook As Workbook
        Set eWorkbook = ThisWorkbook
        For Each SubFolder In Folder.SubFolders
                DoFolder SubFolder
        Next
    Dim File
                    For Each File In Folder.Files
                        Application.StatusBar = Folder
                        MsgBox (File)
                                If File.Name Like "*Example*" Then
                                    Set iWorkbook = Workbooks.Open(Filename:=File)
                                        y = iWorkbook.Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row
                                        x = iWorkbook.Worksheets(1).Cells(6, Columns.Count).End(xlToLeft).Column
                                            With iWorkbook.Worksheets(1).Range(Cells(6, 1), Cells(y, x)): .Copy: End With
                                    eWorkbook.Activate
                                    z = eWorkbook.Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1
                                        With eWorkbook.Worksheets(1).Range("A" & z): .PasteSpecial Paste:=xlPasteAll: End With
                                        Application.CutCopyMode = False
                                iWorkbook.Close savechanges:=False
                                End If
                    Next
End Sub