Am I setting up the loop correctly to get data from several workbooks into one?

Question

I am setting up VBA code which does the following when run:

1. Runs a loop to open Excel files in a folder, one by one
2. Every time that an Excel file is opened, a password unprotects the worksheet and unmerges cells
3. Once the cells are unmerged, the data from the Excel file is copied and pasted to specific cells in the workbook where the VBA code is originally saved/stored
4. Upon pasting the data, the opened Excel workbook from the loop now closes (not necessary to save), and the next workbook is opened, where the next set of data is placed one row below the previous row

The VBA code is run from a workbook which always remains open, we can call it "Workbook 1.xlsm" In this instance for the code below, the use of wbPaste as the active workbook is intended to reference "Workbook 1.xlsm". Because the name of the workbook is going to change every month that this is run, I wanted to declare a workbook by using a naming convention that would reference the name, regardless of what the workbook is called.

The files that are in the folder can have various names, and could be in the hundreds of total files. I have declared most of the variables and have had success in getting the Excel workbooks to open from the folder. Unprotecting the sheet, and unmerging the cells has given some problems, however. I think that the issue that I am experiencing comes with looping the opening of the workbooks and which workbook is considered "active" at the time.

    Sub OpenFilesForExtraction()


    'declaration of variables
    Dim myFolder As String
    Dim myFile As String
    Dim wbCopy As Workbook
    Dim wbPaste As Workbook
    Dim lastRow As Long


    'setting up name of folder and file type (any Excel file in folder) for the loop
    myFolder = "C:\Users\Me\Desktop\Folder 1\"
    myFile = Dir(myFolder & "*.xl??")
    lastRow = 3

    'start of loop
    Do While myFile <> ""

    Workbooks.Open fileName:=myFolder & myFile


    'wbCopy is the Excel file that gets unprotected, unmerged and data is copied from. wbPaste will be where the data gets copied to.  wbPaste is referencing the workbook where the macro is stored.  By declaring these files in the loop, wbCopy should take on the name of the next file opening from the folder

     Set wbCopy = Workbooks(myFile)
     Set wbPaste = ActiveWorkbook


    'Unprotecting and unmerging from the file wbCopy, that was opened by the loop statement

         wbCopy.Unprotect Password:="Password1"
         wbCopy.Unprotect
         Range("C15:E15").Select
         Selection.UnMerge
         Range("H15:J15").Select
         Selection.UnMerge
         Range("C17:E17").Select
         Selection.UnMerge
         Range("B23:C23").Select
         Selection.UnMerge
         Range("B29:C29").Select
         Selection.UnMerge
         Range("B31:J37").Select
         Selection.UnMerge


    'Copying and pasting the information from the files that are being opened to the file wbPaste.  Note that the range for where the value is pasted is determined by the value of "lastRow" variable, which is designed to paste information starting with the cells in row 3, then moving to row 4, row 5, and so on....

    wbCopy.Range("C13").Value = wbPaste.Range("A" & lastRow).Value
    wbCopy.Range("C15").Value = wbPaste.Range("B" & lastRow).Value
    wbCopy.Range("H15").Value = wbPaste.Range("D" & lastRow).Value
    wbCopy.Range("C17").Value = wbPaste.Range("I" & lastRow).Value
    wbCopy.Range("J17").Value = wbPaste.Range("H" & lastRow).Value

    wbCopy.Close


    lastRow = lastRow + 1

    myFile = Dir


    Loop

    End Sub

The program reaches a point where it will open up the first file from the folder, however, I get an immediate error after that. I think that there are two potential reasons.

First, I am not certain if I should use anything related to ActiveWorkbook. The reason why is because as I loop through opening the Excel documents in the folder, the VBA code may not understand which is meant to be the ActiveWorkbook at certain times.

Second, the Unmerging and copy/paste of values is where this will stop the program. I have had some chances to allow the cells to unmerge, but I think it came at the cost of calling out the wbCopy file as an ActiveWorkbook, when it really isn't meant to be called out as an active workbook.

Answer 1

There are a number of issues here

1. Relying on ActiveWorkbook when opening books changes what's active
2. Using Select
3. Your copy/paste is reversed
4. Unnecessary second Unprotect
5. Not using ThisWorkbook (you say you specifically want to paste into the book containing the VBA code)
6. Refering to Range's on Workbooks, instead of Worksheets

Your code, refactored

Sub OpenFilesForExtraction()
    'declaration of variables
    Dim myFolder As String
    Dim myFile As String
    Dim wbCopy As Workbook
    Dim wsCopy As Worksheet
    Dim wsPaste As Worksheet
    Dim lastRow As Long

    'setting up name of folder and file type (any Excel file in folder) for the loop
    myFolder = "C:\Users\Me\Desktop\Folder 1\"
    myFile = Dir(myFolder & "*.xl??")
    lastRow = 3

    'start of loop
    Set wsPaste = ThisWorkbook.Worksheets("NameOfSheetToPasteOn")

    Do While myFile <> vbNullString
        'wbCopy is the Excel file that gets unprotected, unmerged and data is copied from. wbPaste will be where the data gets copied to.  wbPaste is referencing the workbook where the macro is stored.  By declaring these files in the loop, wbCopy should take on the name of the next file opening from the folder
        Set wbCopy = Workbooks.Open(Filename:=myFolder & myFile)

        'Unprotecting and unmerging from the file wbCopy, that was opened by the loop statement
        wbCopy.Unprotect Password:="Password1"
        Set wsCopy = wbCopy.Worksheets("NameOfSheetToCopyFrom")
        With wsCopy
            'wbCopy.Unprotect
            .Range("C15:E15").UnMerge
            .Range("H15:J15").UnMerge
            .Range("C17:E17").UnMerge
            .Range("B23:C23").UnMerge
            .Range("B29:C29").UnMerge
            .Range("B31:J37").UnMerge

            'Copying and pasting the information from the files that are being opened to the file wbPaste.
            'Note that the range for where the value is pasted is determined by the value of "lastRow" variable,
            'which is designed to paste information starting with the cells in row 3, then moving to row 4, row 5, and so on....
            wsPaste.Range("A" & lastRow).Value = .Range("C13").Value
            wsPaste.Range("B" & lastRow).Value = .Range("C15").Value
            wsPaste.Range("D" & lastRow).Value = .Range("H15").Value
            wsPaste.Range("I" & lastRow).Value = .Range("C17").Value
            wsPaste.Range("H" & lastRow).Value = .Range("J17").Value

        End With
        wbCopy.Close False

        lastRow = lastRow + 1

        myFile = Dir
    Loop

End Sub