Copy/Paste Between Workbooks


Board Regular
Mar 16, 2018
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'

End If

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.
Upvote 0
I just added my 4 favorite people on this forum =).... Would anyone be able to assist?
Upvote 0
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
Upvote 0

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
    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
    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
                                    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
End Sub
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back