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]

How about:

Change "Summary" and "C:\books\" for your data.

Sub combine_multiple_workbooks()
  Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
  Dim sFile As Variant, sPath As String
  Dim LastRow1 As Long, LastRow2 As Long, LastCol2 As Long
  Application.ScreenUpdating = False
  
  Set wb1 = ThisWorkbook
  Set sh1 = wb1.Sheets("Summary")
  sh1.Cells.ClearContents
  sh1.Range("A1").Value = "File"
  sPath = "C:\books\"
  sFile = Dir(sPath & "*.xls*")
  
  Do While sFile <> ""
    Set wb2 = Workbooks.Open(sPath & sFile)
    For Each sh2 In wb2.Sheets
      If UCase(Left(sh2.Name, 6)) = UCase("Phase1") Then
        LastRow2 = sh2.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
        LastCol2 = sh2.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Column
        LastRow1 = sh1.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row + 1
        sh2.Range("A2", sh2.Cells(LastRow2, LastCol2)).Copy
        sh1.Range("B" & LastRow1).PasteSpecial xlPasteValues
        sh1.Range("A" & LastRow1).Resize(LastRow2 - 1).Value = sFile
      End If
    Next
    wb2.Close False
    sFile = Dir()
  Loop
End Sub

 

[jordanburch]

Thanks Dante, I really appreciate that. For some reason it is erroring out on the Summary part I even changed my tabs to that name on the blank workbook that I want to import everything to. I also tried to change it to the name of the source workbooks and it still errored out there. The source workbooks have several different tab names, so I would like to import the first tab of each workbook only. It does not matter the name of the tab on the file which im importing everything to. Could you please help me make those updates? THANKS again!

 

[DanteAmor]

. I would like it to combine multiple spreadsheets starting with the name Phase1.

What does the error say?
It would help if you specify which columns you want to copy.
In which column do you always have data?
Your original request says "phase" sheet.
Please your comments.

 

[jordanburch]

oh ok thats the issue then my bad. I want the spreadsheets imported only with the name Phase1. Then inside that spreadsheet I only want the first sheet copied over onto the master file. I want columns a:ae copied over starting at row 2 and pasting with the starting row 2, but the next file would simply find the last row and then append to that and would copy from the source file starting at row 2 and append it to whereever the file left off from the file before. Does that make sense?

 

[DanteAmor]

I want the spreadsheets imported only with the name Phase1

You can put some examples of that.

In which column do you always have data?

Please answer that.

 

[jordanburch]

In columns A:AE is where the data is.

im sorry man I keep saying spreadsheets I meant to say workbooks.

IE

Phase1_cl.xls
Phase1_co.xls
Phase1_IN.xls

The only constant is the Phase1 that is the name of the workbook not spreadsheet. I would like only the first tab inside the workbook. The name of that tab changes across each workbook.

hopefully that makes sense.

Thanks again

Jordan

 

[DanteAmor]

In which column do you always have data?
Please answer

 

[jordanburch]

In columns A:AE is where the data is.

 

[DanteAmor]

In columns A:AE is where the data is.

Sorry, I didn't explain myself. I need to know if any of these columns always have data, for example column A always has data or some cells are empty, so column B always has data or some cells are empty, then C always has data. Does any column always have data?