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

 

[DanteAmor]

There is always data in column B fyi.

Let's try column B.

Is the process copying information from a book or not copying anything on the summary sheet?

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 GoTo nextfile
  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
nextfile:
    wb2.Close False
    sFile = Dir()
  Loop
End Sub

 

[Fluff]

Oops, missed that line
Maybe one of the files being opened has a blank sheets(1).
@jordanburch
Make sure none of the files has a blank first sheet, also check for hidden sheets.

 

[DanteAmor]

Remember to change the folder.
In the file with the macro you must have a sheet called "Summary"
And if there is an error, on which line of the macro does it stop?

 

[DanteAmor]

Make sure none of the files has a blank first sheet, also check for hidden sheets.

With the macro in post#21 it doesn't matter if the sheet is empty.
Nor does it matter if sheet 1 is hidden, the macro still takes the information.

I do not know what problem the OP file has, I am waiting for the OP tell me if it saved information from other books to discard the book that has problems and specifically review that file to detect the problem.

 

[jordanburch]

that gave me the same result. It errors out on one spreadsheet after six lines, but then opens the next spreadsheet after the one it grabbed six lines from. It does show the file name on the left column down further than the six lines it imports, but those are all blank other than the file name in column a.

 

[jordanburch]

the book that it gave me the six lines on wont open for some reason. Theres data there, but I cant open it I wonder if its corrupt somehow?

 

[jordanburch]

it tries to open it but when I click on the excel icon in my bar at the bottom of my screen to where it shows the little preview box it just shows it as being a white or blank spreadsheet and I click on it and it does not open or load the workbook.

 

[DanteAmor]

You can try test files.
Create 2 files with the names:
Phase1_cl.xls
Phase1_co.xls
On the first sheet of each book write data in columns A and B.
Put the 2 files in a new folder.
Try the macro again with the new folder with only those 2 files and check the result.

 

[jordanburch]

ok so I closed all workbooks. I opened the file it errored out on. I believe it is erroring because it has a filter in column y only showing those six lines and it is trying to pull the rest of the data but its not allowing it to.

 

[DanteAmor]

ok so I closed all workbooks. I opened the file it errored out on. I believe it is erroring because it has a filter in column y only showing those six lines and it is trying to pull the rest of the data but its not allowing it to.

You can try test files.
Create 2 files with the names:
Phase1_cl.xls
Phase1_co.xls
On the first sheet of each book write data in columns A and B.
Put the 2 files in a new folder.
Try the macro again with the new folder with only those 2 files and check the result.