Error when merging multiple workbooks into one workbook/multiple sheets

[suzyquzee]

This is my first post. I found the below code on this site that allows me to merge multiple workbooks in one workbook placing each workbook on a separate sheets. This works perfectly when run at home on my pc which is running excel 2007, however at work when I run the same code (in Excel 2007, just modifying the MyPath to appropriate destination) I get the following "Excel cannot insert sheets into the destination workbook because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can select the data then use copy and paste commands to insert it into the sheets of another workbook." Any suggestions as to what I might be doing wrong?

Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String
On Error GoTo Errorcatch
 
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
MyPath = "D:\Dept E\Div E1\1-Aug2011"
Set wbDst = Workbooks.Add(xlWBATWorksheet)
strFilename = Dir(MyPath & "\*.xls", vbNormal)
 
If Len(strFilename) = 0 Then Exit Sub
 
Do Until strFilename = ""
 
Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)
 
Set wsSrc = wbSrc.Worksheets(1)
 
wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
 
wbSrc.Close False
 
strFilename = Dir()
 
Loop
wbDst.Worksheets(1).Delete
 
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
Errorcatch:
MsgBox Err.Description
 
End Sub

 

[Trevor G]

Welcome to the Forum,

Most companies set Office 2007 to work in 2003 mode, so if you use the Office Button and go into Options and Save you can change it to Excel Workbook which is 2007, then when you run the code it should work, you might have to save the destination workbook as 2007 rather 2003

 

[suzyquzee]

Changing that option did provide some progress, thanks for the quick reply. It did complete the merge of my 1st file in the folder, however it stopped after that, it left the 1st file open so can you think of any reason it would stop at that point?

 

[suzyquzee]

The documents I am merging do include macros. I did notice that when running the merge after changing the Save format to xlsx it did prompt me with notification that the document about to be merged contained macros and I selected "Yes, continue the operation." I am suspecting that is the reason that only the 1st file completes the merge (one would have to accept that for each of the files but I dont get that prompt). I would not be to change any security settings, is there anyway around this or is there another option that I should consider?

 

[Trevor G]

If the workbooks had macros and where saved as xlsm and you stated Save As xlsx then the macros will disappear.

How many workbooks are in the folder?

Have you looked to test this in a dummy folder with some workbooks in them first?

In the VBA screen try using the step into code to see how well it runs.