VBA Help: Copying worksheets from multiple workbooks to the current workbook.

[davidtaylor598]

Hi all,

I have a macro that allows you to select files from a directory and copies each worksheet into the current file. However, it is falling down as I do not need all of the worksheets in each workbook and some of the worksheets have assigned values / formulae that are not copying across as they are already in use in the current file.

I think the only way to solve this will be to copy over specific worksheets from each workbook, is this the case?


Another idea I have is to somehow copy over all the worksheets form all the workbooks selected, then sort the ones I need, and then delete the ones to the right of the last one, will this work? If this could work I would need to do something else to allow me to copy over the workbooks that have assigned values / formulae.


Here is my code at the moment:

Sub MergeWorkbooks()
 
Z = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", MultiSelect:=True)
 
'Open loop for action to be taken on all selected workbooks.

For x = 1 To UBound(Z)
Set WB = Workbooks.Open(Z(x))
WB.Sheets.Copy After:=ThisWorkbook.Sheets(1)
WB.Close False
Next x
 
'''''''''''''This next part orders the sheets''''''''''
Sheets("Valuation Summary").Move After:=Sheets(1)
Sheets("Assets_And_Liabilities_Europe").Move After:=Sheets(2)
Sheets("Securities_at_Value").Move After:=Sheets(3)
Sheets("Foreign_Currency").Move After:=Sheets(4)
Sheets("Acc_Gross_Inc").Move After:=Sheets(5)
'Sheets("Cap_Shares_Sold_Receivable").Move After:=Sheets(6)

'There are over 20 sheets but I just put in the first 6 to save space.
 
 
'''''''''''''' This next part deletes any hidden sheets without having a prompt message.

Application.DisplayAlerts = False
i = 1
While i <= Worksheets.Count
If Not Worksheets(i).Visible Then
Worksheets(i).Delete
Else
i = i + 1
End If
Wend
Application.DisplayAlerts = True
''''''''''''''''

End Sub