combine multiple workbooks first sheet start at row 2 and appending to the bottom

[jordanburch]

Hey guys

been trying different codes most of the day. Have not found anything that would work. I would like it to combine multiple spreadsheets starting with the name Phase1. I would then like it to create a column with the file title name to the left to remeber which file the record came from. I also would like it to start on row 2. I have had some success but nothing seems to pop up with everything I need. Can anyone help?

Jordan

 

[Fluff]

With the macro in post#21 it doesn't matter if the sheet is empty.

Agreed, but I hadn't seen that when I posted.
For reference, your error handling will only work once. If a 2nd error occurs the code will crash.

 

[DanteAmor]

I believe it is erroring because it has a filter in column

It doesn't matter if the data is filtered.
The macro works fine.
Some of your files have another type of problem.

 

[DanteAmor]

For reference, your error handling will only work once. If a 2nd error occurs the code will crash.

Ok, Let's try the following

Sub combine_multiple_workbooks()
  Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
  Dim sFile As Variant, sPath As String, LastRow1 As Long, LastRow2 As Long
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Set wb1 = ThisWorkbook
  Set sh1 = wb1.Sheets("Summary")
  sh1.Cells.ClearContents
  sh1.Range("A1").Value = "File"
  sPath = "C:\trabajo\books\"
  sFile = Dir(sPath & "Phase1*.xls*")
  On Error Resume Next
  Do While sFile <> ""
    Set wb2 = Workbooks.Open(sPath & sFile)
    Set sh2 = wb2.Sheets(1)
    LastRow2 = sh2.Range("B" & Rows.Count).End(xlUp).Row
    LastRow1 = sh1.Range("B" & Rows.Count).End(xlUp).Row + 1
    sh2.Range("A2:AE" & LastRow2).Copy
    sh1.Range("B" & LastRow1).PasteSpecial xlPasteValues
    sh1.Range("A" & LastRow1).Resize(LastRow2 - 1).Value = sFile

    wb2.Close False
    sFile = Dir()
  Loop
End Sub

 

[jordanburch]

ok that worked better no errors this time. I unfiltered that data and it pulled it all through just fine, not sure why though since you said it should not matter. Now the only last problem is its not pulling every file in that folder that starts with Phase1 its skipping 3 of them.

 

[jordanburch]

im beginning to wonder if this is a memory issue. It seems the files that are pulling through correctly are less than 50 lines other than the very first file which is 80 lines. The three files that arent pulling through are greater than 80 lines but less than 140. Could that be my issue?

 

[DanteAmor]

every file in that folder that starts with Phase1 its skipping 3 of them.

Check that they really start with Phase1, that it doesn't have spaces or something like this: Phase 1

The three files that arent pulling through are greater than 80 lines

There is no problem with the number of lines, there may be thousands of lines, but there must be data in column B.

 

[jordanburch]

they all start with Phase1 for sure. Its skipping them somehow.

 

[jordanburch]

its very strange nothing seems to be different about those files. They arent hidden or filtered or password proctected. They are not pulling through is all. All of the files are located on the shared drive if that makes any sort of difference.

 

[jordanburch]

ok so I took out the on error resume part to see where the error was. It did open that file that I wanted to import that it had been skipping. It gave me this line was the error code

sh1.Range("A" & LastRow1).Resize(LastRow2 - 1).Value = sFile

 

[DanteAmor]

this line was the error code

And what does the error message say?

Does the macro work for other files?
If so, then the file has a problem, you must copy the information to a new book, save the new book in the folder and delete the old book.