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