Merging multiple workbooks

Hi Everyone

I have 7 workbooks (single tab) that I need to merge into the master workbook. I have 'put together' a macro that opens the files and moves them to into the master.

As this may be performed repeatedly (the master is split and merged at intervals) is there a way to then delete the files once they have been moved. It just means that when the splitting occurs I get the message 'replace existing file?' which it would be good if I could skip.

The other query was if there was a way to automatically select the seven files (same path as active workbook) and move them without the dialogue box (didn't think you could use an array with workbooks, just sheets?).

I know, there is lots of code on this forum but as I'm a newbie find it too difficult to read and amend. Hence the simplicity of my code (except the bottom bit which I got help with)!!

Would be greatful for any help

 Dim MyWk As String
    MyWk = "M" & Sheets("Checks").Range("$B$2").Value & " Forecast Outturn.xls"
    
    Application.ScreenUpdating = False
    
    Call OpenExcelFile
    
    Windows("Resources.xls").Activate
    Sheets("Resources").Select
    Sheets("Resources").Move After:=Workbooks(MyWk).Sheets("Checks")

    Windows("Comm.xls").Activate
    Sheets("Comm").Select
    Sheets("Comm").Move After:=Workbooks(MyWk).Sheets("Checks")

    Windows("Strategy.xls").Activate
    Sheets("Strategy").Select
    Sheets("Strategy").Move After:=Workbooks(MyWk).Sheets("Checks")
    
    Windows("Corp.xls").Activate
    Sheets("Corp").Select
    Sheets("Corp").Move After:=Workbooks(MyWk).Sheets("Checks")
    
    Windows("BDU.xls").Activate
    Sheets("BDU").Select
    Sheets("BDU").Move After:=Workbooks(MyWk).Sheets("Checks")
    
    Windows("Hosting.xls").Activate
    Sheets("Hosting").Select
    Sheets("Hosting").Move After:=Workbooks(MyWk).Sheets("Checks")
    
    Windows("Quality.xls").Activate
    Sheets("Quality").Select
    Sheets("Quality").Move After:=Workbooks(MyWk).Sheets("Checks")

    Range("A1").Select
    Call Unprotect
    calculate
End Sub



Sub OpenExcelFile()

      Dim vFile As Variant

      'Showing Excel Open Dialog Form

     vFile = Application.GetOpenFilename("Excel Files (*.xls*)," & _
     "*.xls*", 1, "Select Excel File", "Open", True)
      
      'If Cancel then exit
     If TypeName(vFile) = "Boolean" Then
         Exit Sub
     End If

     'Open selected file
     For i = 1 To UBound(vFile)
     Workbooks.Open vFile(i)
    Next i

End Sub

Sub tst7()
  for j=1 to 7
    with workbooks.open "C:\" & choose(j,"Resources","Comm","Strategy") & ".xls"
      .Sheets("Resources").Move After:=Thisworkbook.Sheets("Checks")
      .close False
    end with
  Next
End Sub

NB. you have to complete choose(j,"Resources","Comm","Strategy") to 7 arguments.
change C:\ into the directory where the files are located.

Hi snb,

Thanks for your help!

I've amended the code and tried to add in the path (copied from another bit of code), the path will change each month hence the below but think I may have the syntax wrong? Or is it excel issue because as soon as I copied the code in it turned red?

Sub tst7()
    MDir = ThisWorkbook.Path
  For j = 1 To 7
    with workbooks.open MDir & "\" & choose(j,"Resources","Comm","Strategy","Corp","BDU","Hosting","Quality") & ".xls"
      .Sheets("Resources").Move After:=ThisWorkbook.Sheets("Checks")
      .Close False
    End With
  Next
End Sub

No need for variables.

Sub tst7()
  For j = 1 To 7
    with workbooks.open( ThisWorkbook.Path & "\" & choose(j,"Resources","Comm","Strategy","Corp","BDU","Hosting","Quality") & ".xls")
      .Sheets("Resources").Move After:=ThisWorkbook.Sheets("Checks")
      .Close False
    End With
  Next
End Sub

Hi snb

I've tried running it I get a run time error at line

.Close False

but it does move the worksheet. Do I need to repeat the 'move' line for each of the worksheets?

Thanks

Sub tst7()
  For j = 1 To 7
    with workbooks.open( ThisWorkbook.Path & "\" & choose(j,"Resources","Comm","Strategy","Corp","BDU","Hosting","Quality") & ".xls")
      for each sh in .sheets
         Sh.Move After:=ThisWorkbook.Sheets("Checks")
      next
      .Close False
    End With
  Next
End Sub

Hi,

Sorry to be a pain! I'm still getting a runtime error

Sh.Move After:=ThisWorkbook.Sheets("Checks")

Thanks for your help so far, but I've tried running and it executes perfectly but it just adds new sheets. I thought is was possible to move sheets between books but I think it may go back to my original query of whether it's possible to not have to click the files to be opened (i.e. dialogue box) as the filenames are static.

From other posts I've read and other sites I thought it was possible but I haven't got a hope in hell of understanding the code, maybe in a year or two..*frustrated sigh*