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

 

[jordanburch]

it says run time error 1004 aplication defined or object defined error

 

[jordanburch]

so I tried the saving the workbooks seperately and it still has the same error on the same line

 

[DanteAmor]

Does the macro work for other files?

Please, answer that question.

 

[jordanburch]

yes it does. There are 8 total files but its not working on three of them.

 

[Fluff]

On the workbooks where it fails, do you have any data in column B of the first sheet?
If so check that you don't have any hidden sheets in that workbook.

 

[DanteAmor]

but its not working on three of them.

You can upload one of those books to the cloud to review it.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[jordanburch]

On the workbooks where it fails, do you have any data in column B of the first sheet?
If so check that you don't have any hidden sheets in that workbook.

yes there is data on every workbook in b where it fails.

ou can upload one of those books to the cloud to review it.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

unfortunately I cannot so that sucks! Its sensitive material. I guess I will have to figure it out on my own. Any suggestions on how to proceed? It might work itself out next month when the spreadsheets are avaialable. Thanks for any help and all previous help!

 

[Fluff]

You didn't answer this part of my question

If so check that you don't have any hidden sheets in that workbook.

I should also have said check for very hidden sheets as well.

 

[jordanburch]

You didn't answer this part of my question

I should also have said check for very hidden sheets as well.

i think you got it right there bud. There is a very hidden one that is sheet 1 hence thats why its not pulling in. so How do we deal with that in the code to take the first visible sheet?

THANKS!

 

[DanteAmor]

I understand the confidentiality of your information, I'm just trying to help you.

How do we deal with that in the code to take the first visible sheet?

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
  Dim sh As Worksheet
  
  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*")
  Do While sFile <> ""
    Set wb2 = Workbooks.Open(sPath & sFile)
    For Each sh In wb2.Sheets
      If sh.Visible = -1 Then
        Set sh2 = wb2.Sheets(1)
        Exit For
      End If
    Next
    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