VBA: Compile multiple workbooks to one sheet, unknown error

[Rymare]

I have some code that a lovely contributor on here was kind enough to make for me, and it takes a list of excel paths (which can be a long list or short), opens them, is supposed to copy all the rows that have data (because some will have more data than others) and then add them to the bottom of a list in another sheet in another workbook.

But I think I did something because for some of the files it will only copy the first row of whatever excel file it's opening? On others it will copy more. I have no idea why this is happening and haven't been able to get a solution.

Example:

I have 3 files listed
c:\documents\A.xlsx
c:\documents\B.xlsx
c:\documents\C.xlsx

They all only have 1 sheet in them.
A had 100 rows of data
B has 2
C has 40
The code is supposed to copy those rows with data, put them in the sheet("Addresses") in the workbook running the code, so that in the end I have rows from workbooks A,B,C in the sheet "Addresses" (100+2+40=142 rows).

Instead, what it's doing is sometimes only copying row 1 from B and C and everything from A. Or everything from B and only row 1 from C and the first and last row from A.


I don't know what I did to the poor code! Help?

Sub putinlist()
  Dim Cell As Range
  With Worksheets("ghgh")
    For Each Cell In .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Cells
      If Len(Dir(Cell.Value)) Then
        With Workbooks.Open(Cell.Value)
          Range("A2:X" & Cells(Rows.Count, "A").End(xlUp).Row).copy _
              ThisWorkbook.Worksheets("Addresses").Cells(Rows.Count, "A").End(xlUp)(2)
          .Close SaveChanges:=False
        End With
      Else
        MsgBox "File not found: " & Cell.Value
      End If
    Next Cell
  End With


End Sub

EDIT:
Sometimes Column A (in all the sheets) will have data sometimes it won't. Column D will always have data when the row is used.

 

[Fluff]

Try

   With Workbooks.Open(cell.Value)
      .Range("A2:X" & .Cells(Rows.Count, "D").End(xlUp).row).Copy _
         ThisWorkbook.Worksheets("Addresses").Cells(Rows.Count, "D").End(xlUp)(2)
      .Close SaveChanges:=False
   End With

 

[Rymare]

Try

   With Workbooks.Open(cell.Value)
      .Range("A2:X" & .Cells(Rows.Count, "D").End(xlUp).row).Copy _
         ThisWorkbook.Worksheets("Addresses").Cells(Rows.Count, "D").End(xlUp)(2)
      .Close SaveChanges:=False
   End With

Unfortunately I get a Run-Time Error '438': Object doesn't support this property or method when I use that approach. It highlights that line of code :/

 

[Fluff]

How about

Sub putinlist()
  Dim Cell As Range
  Dim wbk As Workbook
  With Worksheets("ghgh")
    For Each Cell In .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Cells
      If Len(Dir(Cell.Value)) Then
        Set wbk = Workbooks.Open(Cell.Value)
          Range("A2:X" & Cells(Rows.Count, "D").End(xlUp).row).Copy _
              ThisWorkbook.Worksheets("Addresses").Cells(Rows.Count, "D").End(xlUp)(2)
          wbk.Close SaveChanges:=False
      Else
        MsgBox "File not found: " & Cell.Value
      End If
    Next Cell
  End With


End Sub

 

[ffionnah]

Do you happen to have a table with all this data on the different sheets? I have a code I use to pull about 20 sheets onto one page. Originally, I was having the same issue because my table was inhibiting me from pulling everything over.