I need the ability to copy multiple worksheets from one workbook created via Crystal Reports export (ms excel 2003 format) and merge into one worksheet in ms excel 2010. Currently, I am able to use the openfile dialog to open the 2003 workbook, create a new 2010 workbook, and copy the first 2003 worksheet into sheet 1 of the 2010 workbook.

However, when I copy the second 2003 worksheet, I get the famous "The information cannot be copied because the copy area and the paste area are not the same size and shape.

I am using VBA to perform this activity, but I am not able to get past this error. We can have 2003 workbooks that can have anywhere from 3 to 35 worksheets. The first worksheet will contain the header row and then the maximum number of rows allowed on the worksheet. The other worksheets will contain the maximum number of rows on the worksheet with the last worksheet containing less than the maximum. The number of columns can be varied, but I do not have to worry about copying hiddens rows/columns or copying formulas. It is just a straight copy and paste. However, the sheets in the 2003 workbook must be copied in order from sheet 1, sheet 2, sheet 3, etc as this data will usually be in some sort of sorted order.

        With wbkOldWorkBook
        
            .Activate

            For Each wsOldSheet In wbkOldWorkBook.Worksheets
                wsOldSheet.Activate
                
                wbkNewWorkBook.Activate
                wbkNewWorkBook.Sheets(1).Select
                
                wbkOldWorkBook.ActiveSheet.UsedRange.Offset(0).Clear
                    
                    wsOldSheet.UsedRange.Copy
                    
                    wbkNewWorkBook.Activate
                    wbkNewWorkBook.Sheets(1).Select
                    
                        With Range("A65536").End(xlUp).Offset(1, 0)
                            .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                False, Transpose:=False
                        '.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
                        '    False, Transpose:=False
                        End With
                    
            Next
        
        End With
So my question is, what am I doing wrong or what am I missing???

Thanks!!!