Run-time error '1004': when copying sheets between two different workbooks

[mlindquist]

I wrote a macro to copy sheets between two different workbooks and I am getting the below error.

Run-time error '1004':

Excel cannot insert the 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, and then use the Copy and Paste commands to insert it into the sheets of another workbook.

The thing is that there is plenty of data in the destination work book so I don't understand why this error is even popping up.

 

[mlindquist]

I was also not able to do the right click on the tab that I wanted to copy to a new workbook - that's where you do a right click, choose Move or Copy and then change the workbook you are copying to and then check the Create a copy - received the same kind of error. So I don't think it is the macro that is causing the problem unless there is something that can be added to the macro so I don't get this error and can copy the sheets from one workbook to another easily.

Here is my VBA:

' Description:  Macro copies the Confirmation Form and Dispute
'               Reasons sheets to the specified Monthly HireRight
'               Workbook entered in Input box without extension
'
'***************************************************************************
Sub CopySheets()

myWorkbook = InputBox("Enter Workbook Name to Copy Worksheet onto")

 
Workbooks("HirerightInvoice_SplitMacro").Sheets("Confirmation Form").Copy _
    After:=Workbooks(myWorkbook & ".xlsx").Sheets(Workbooks(myWorkbook & ".xlsx").Sheets.Count)
    
Workbooks("HirerightInvoice_SplitMacro").Sheets("Dispute Reasons").Copy _
    After:=Workbooks(myWorkbook & ".xlsx").Sheets(Workbooks(myWorkbook & ".xlsx").Sheets.Count)

Sheets("Confirmation Form").Select

Range("C18:C29").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=DisputeReasons"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
End Sub

 

[Fluff]

Sounds like the destination workbook is an xls file rather than an xlsx or xlsm file.

 

[mlindquist]

Sounds like the destination workbook is an xls file rather than an xlsx or xlsm file.

The destination workbook is an xlsx file.

 

[Fluff]

In that case check that it's not open in compatibility mode.

 

[mlindquist]

In that case check that it's not open in compatibility mode.

How do you do that?

 

[Fluff]

Look at the title bar