runtime error 1004 cant copy sheet from source to dest
excel, vba
Solution
The `.xls` extension is the Excel 2003 workbook format. These support only 65,536 rows and 256 columns.
`.xlsx`,`.xlsb`,`.xlsm` are Excel 2007 workbooks. These support up to 1,048,576 rows and 16,384 columns.
This error is occurring because you are trying to copy a worksheet from a 2007 workbook to a 2003 workbook. This cannot be done because the of the row and column count limitation, even if you are not actually making use of all those extra rows and columns in the sheet being copied.
The solution is to save the `.xlsm` workbook as a `.xls` file or vice versa, and then copy the sheet over. Alternatively, rather than copying the entire sheet, you can copy just the UsedRange from one sheet to another, but risk losing important formatting such as row heights and column widths.
Adding a new workbook will create a workbook in your default save format. You can ensure this is progrmatically set to a .xlsx file by forcing the default save format in VBA and then changing it back so that you don't upset the user:
Dim prevSaveFormat As Long
prevSaveFormat = Application.DefaultSaveFormat
Application.DefaultSaveFormat = 51 'xlsx
Workbooks.Add
Application.DefaultSaveFormat = prevSaveFormat
A full list of file format constants is available at XlFileFormat Enumeration but here are the common ones:
51 = xlOpenXMLWorkbook (without macro's in 2007-2010, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2010, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)
Problem
Really this is curious. Let me try to explain what is going on. I wrote a macro in word that is calling a macro of an excelfile ``` Set excel = CreateObject("excel.application") excel.Visible = True excel.workbooks.Open FileName:=tablePath IDcolumn = excel.Application.Run("main", generatedExelPath) ``` the "main" function being called from the word macro is "simply" generating a new excel-file by copying specific data from the called excel file. So we got 1 word and 2 excel documents. some sheets of excel1 have to be copied to excel2. therefore I use: ``` For i = 0 To UBound(copySheets) Windows(srcWinName).Activate Sheets(copySheets(i)).Activate Set AcSh = ActiveSheet Windows(destWinName).Activate If copySheets(i) = "config" Then AcSh.Copy Before:=Sheets(1) Else AcSh.Copy After:=ActiveSheet End If Next i ``` where copySheets contains the sheets that needs to be copied. But the copy commands throws an error. Something like "can't copy from source to destination cause the destination has less rows and columns than the source file" Atm excel1 is an `xlsm` as it was an `xls` file this error didn't occur. I wonder if this line, which adds the excel2 is the prob ``` Workbooks.Add Template:="Workbook" ``` maybe i should use another template but I don't know which one. If i create an empty `xlsm` file, save it and then use open this empty `xlsm` instead of the command before the error doesn't occur. Hope someone can help me. Ahh and one thing I should mention is: if I run the excel macro as above from the word macro the error occurs but when I close Word and manually execute the excel macro it works. Edit: It works I just changed the excel-options as Alain said. here his post. Oh I know why that's happening. You have to change your default excel format: Orb > Excel Options > |Save| > Save files in this format: "Excel Workbook (.xlsx)". Your current setting will be Excel 97-2003 Workbook (.xls). – Alain Aug 29 at 12:09