Copy and paste Excel sheets

Question

Private Sub CommandButton1_Click()
Dim ws As Worksheet
    With Application.FileDialog(msoFileDialogFilePicker)
       .Show
       If .SelectedItems.Count <> 0 Then
       fldr = .SelectedItems(1)
       End If
    End With
    Sheets.link.Value = fldr
    For i = 1 To Worksheets.Count
        Set ws = Worksheets(i)
        If ws.Cells(2, 1) = "X" Then
            Sheets.ComboBox1.AddItem (ws.Name)
        End If
    Next i
    Workbooks.Open (fldr)
    Sheets.Show
End Sub

Private Sub Add_Click()

Dim x As String
Dim ws As Workbook

x = Right(link.Value, (Len(link.Value) - InStrRev(link.Value, "ild") - 3))

Workbooks("Test.xlsm").Activate

Worksheets(ComboBox1.Value).Copy Before:=Workbooks(x).Worksheets("Contract")

End Sub

So the basic idea is, you click a button on an Excel sheet. The user then finds the file they want to copy the sheets to. It will find all of a specific type of sheet, put it in a forms combobox and open the selected Excel file. Then you choose a sheet from the combobox and copy it from one workbook to the other. It all works until the copying part. I get a long error:

Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns that the source workbook. To move or copy the data to the destination workbook, you can select the data and then use Copy and Paste commands to insert it into the sheets of another workbook.

As a first thought, it is probable that the source worksheet is too big, and the destination workbook is from an older version of Excel. Is that right? — A.S.H
Put something down in the answer section so I can accept that one too, because you were right. I was using and .xlsm, and the one I was copying to was an .xls. When I saved it as an .xlsm, it worked. — Chris
I am glad it helped. Well, dont worry about that, the important is that the issue is solved. Thanks for your kindness anyway ;) — A.S.H
@A.S.H I would suggest you do place it as an answer for future users looking for a similar post can easily find a solution. Regards, — nbayly
@nbayly okay, in fact, I tested it and interestingly, it seems to occur even if the used range is small. It is a compatibility issue, hence I think it deserves a comprehensive answer. Thanks for you suggestion :) — A.S.H

A.S.H A.S.H · Accepted Answer · 2015-11-04T21:07:02

If the destination workbook comes from an older version of Excel (extension .xls for instance, Excel 97 or Excel 2003), the limit of number of rows in old worksheets is 2^16-1, as the row number is encoded on 16 bits. In newer versions, this number is encoded on 32 bits.

Hence, copying a worksheet "as a whole" from a newer version into a workbook from an older version raises this error. From my test, this error occurs even if the actually used range in the copied worksheet is small.

Copy and paste Excel sheets

2 Answers