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

mlindquist

New Member
Joined
Sep 6, 2019
Messages
15
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.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

mlindquist

New Member
Joined
Sep 6, 2019
Messages
15
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:

VBA Code:
' 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Sounds like the destination workbook is an xls file rather than an xlsx or xlsm file.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Look at the title bar
1611672559144.png
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top