macro to merge worksheets "select method of range class failed"

Hi all,

I borrowed and modified for my purposes the below code. I want to combine the data from a bunch of worksheets into one worksheet. I have many workbooks that each have a worksheet called "extracteddata". I want to open each workbook, copy the non-blank rows from 'extracteddata" and then paste it in to the next non-blank row of a master worksheet. The code below worked when I tested it to open, copy and paste the data from 2 sample files. But when I increased the test to 6 or so files, the code got stuck at file #3. The error I get is "select method of range class failed". All the files have the same structure, so I cannot figure out why the code works for workbooks 1 and 2; but then gets hung up at workbook #3. A clue may be that when the code gets stuck it is visible that 2 ranges of data from 2 different worksheets on the offending workbook are selected. The data from file #1 and #2 are copied into the master correctly, so clearly the macro worked correctly for those 2 files. Any ideas?

Sub MergeWorkbooks()
  Dim wbkCur As Workbook
  Dim wbkAdd As Workbook
  Dim strPath As String
  Dim strFile As String
  Dim jcount As Integer
  Set wbkCur = ActiveWorkbook
  With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show Then
      strPath = .SelectedItems(1)
    Else
      MsgBox "You didn't select a folder!", vbExclamation
      Exit Sub
    End If
  End With
  Application.ScreenUpdating = False
  If Right(strPath, 1) <> "\" Then
    strPath = strPath & "\"
  End If
  strFile = Dir(strPath & "*.xls*")
  Do While strFile <> ""
    Set wbkAdd = Workbooks.Open(strPath & strFile)
    'this next line draws on a value in the workbook to set a variable called jcount which is later used to determine how many rows of data to copy
    jcount = Worksheets(8).Range("a1").Value
    wbkAdd.Worksheets("extracteddata").Range("a1:dd1").Select
    Selection.Resize(jcount).Select
    Selection.Copy
    Workbooks("master_data").Worksheets(1).Activate
    Sheets(1).Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial _
        Paste:=xlPasteValues
    wbkAdd.Close SaveChanges:=False
    strFile = Dir
  Loop
  Application.ScreenUpdating = True
End Sub

Thanks for the thought. I did check that. Check out the solution proposed by another user. It worked.

Thank you for your suggestion! I did edit the paste code. I got an error in that line running exactly as you wrote it. Also I have inserted application.cutcopymode = false to empty the clipboard automatically.

Option Explicit

Sub MergeWorkbooks()
Dim wsMaster As Worksheet, wbkAdd As Workbook
Dim strPath As String, strFile As String, jcount As Long

    Set wsMaster = ActiveWorkbook.Worksheets(1)
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then
            strPath = .SelectedItems(1)
        Else
            MsgBox "You didn't select a folder!", vbExclamation
            Exit Sub
        End If
    End With

    Application.ScreenUpdating = False
    If Right(strPath, 1) <> "\" Then
        strPath = strPath & "\"
    End If

    strFile = Dir(strPath & "*.xls*")
    Do While Len(strFile) > 0
        Set wbkAdd = Workbooks.Open(strPath & strFile)
        'this next line draws on a value in the workbook to set a variable called jcount which is later used to determine how many rows of data to copy
        jcount = wbkAdd.Worksheets(8).Range("a1").Value
        wbkAdd.Worksheets("extracteddata").Range("a1:dd1").Resize(jcount).Copy
        wsMaster.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        wbkAdd.Close False
        strFile = Dir
       
    Loop

  Application.ScreenUpdating = True
End Sub