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]

Try the following.
In a book put the macro and create a sheet called "summary".
Change "books" to the folder where you have the books.

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:\books\"
  sFile = Dir(sPath & "Phase1*.xls*")
  
  Do While sFile <> ""
    Set wb2 = Workbooks.Open(sPath & sFile)
    Set sh2 = wb2.Sheets(1)
    LastRow2 = sh2.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    LastRow1 = sh1.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).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 I will try this out. There is always data in column B fyi. sorry I had to restart my laptop and it took forever.

 

[jordanburch]

ok so it did work. Thanks again for that. It looks like its erroring out because the file is slightly different. The only difference I noticed was that instead of starting on row 2 it started on row 3. So I deleted row 1 for them to look the same and it still gives the same error. There are some hidden cells and columns in the file it is erroring out in, could that be the problem?

The error message is

object variable or with block variable not set

 

[DanteAmor]

object variable or with block variable not set

Press the "debug" button, which line of the macro is highlighted?

 

[jordanburch]

it just gives me ok and cancel buttons at the bottom theres no debug option

 

[DanteAmor]

Try this

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:\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.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    LastRow1 = sh1.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).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

 

[jordanburch]

ok that seemed like it worked a little better now its giving me more detail on the message.

Run Time error 91
Object Variable or with block variable not set

ok help

help

Object variable not set (Error 91)

Office VBA reference topic

docs.microsoft.com

 

[Fluff]

Probably coming from this line

    LastRow1 = sh1.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row + 1

As sh1 is blank the first time through the Do loop.

 

[DanteAmor]

sh1.Cells.ClearContents
sh1.Range("A1").Value = "File"

As sh1 is blank the first time through the Do loop.

But sh1 is not blank because I put a header in cell A1

 

[jordanburch]

it seemed like it skipped a file. The last time it errored out on this particular file and this time it looks like it skipped it all together. I dont know if that helps any. Im trying to do my best but im not near as advanced as you fellas. Hopefully this information helps.