Hi, I hope someone can help.
I have a workbook (Book1.xls) which, by using a button on a form, opens a second workbook (Book2.xls) and copies data from it into the original (Book1.xls).
The second workbook automatically opens a form when it is opened, and this is bringing the process to a halt before I get to the bit where I want to close Book2.xls. The code works fine but you have to manually close the form in the newly opened workbook before the process completes. Because the code will ultimately be used for duplicating more than one workbook this is not desirable - I want the process to complete without the user having to manually close down the forms which pop up every time a new file is opened ready for copying.
Can anyone tell me how to select the open form in Book2.xls and force its closure so the code continues to run without the user having to manually close each form.
Thanks in advance for any responses
Here is the code I have tried using which does NOT work:
Private Sub CommandButton13_Click()
Dim WsOld As Worksheet, WsNew As Worksheet
NewWorkBookName = "Book1.xls"
OldWorkBookName = "Book2.xls"
ChDir ActiveWorkbook.Path 'change to the correct directory ready to open Book2.xls
Workbooks.Open (OldWorkBookName) 'open Book2.xls
Workbooks(OldWorkBookName).Activate 'activate Book2.xls in the hope this will let me close the offending form
Unload FormMainMenu 'this code does not close the form which is stopping the procedure from continuing
Set WsOld = Workbooks(OldWorkBookName).Sheets(1)
Set WsNew = Workbooks(NewWorkBookName).Sheets(1)
WsOld.Range("A2:E2000").Copy Destination:=WsNew.Range("A2")
Workbooks(OldWorkBookName).Close SaveChanges:=False 'This only works once the user has closed the FormMainMenu form
End Sub
Bookmarks