Excel VBA: Copying from file to file

rodcon

Thank you Mr. Dawson for your help with copying formats using Excel VBA. But I still cannot copy from a workbook that is part of a separate .xls file. The code works between worksheets of a workbook in one .xls file. If I try to copy from one file to another I get an "Error '9': Subscript out of range". This occurs even if I specify the path.

Anyone have any thoughts

'referencing separate workbooks, copies the range and its formats -- Ends with error
Workbooks("C:\Folder\Source.xls").Sheets("sourceSheet").Range("A1:IL36").Copy
Workbooks("C:\Folder\Target.xls").Sheets("destSheet").Range("A1:IL36").PasteSpecial (xlPasteAll)
Workbooks("C:\Folder\Source.xls").Sheets("sourceSheet").Range("A:IL").Copy
Workbooks("C:\Folder\Target.xls").Sheets("destSheet").Range("A:IL").PasteSpecial (xlPasteFormats)

'within same workbook, successfully copies the range and its formats of ConfigRelease
Sheets("sourceSheet").Range("A:HI").Copy
Sheets("destSheet").Range("A:HI").PasteSpecial (xlPasteFormats)
Sheets("sourceSheet").Range("A1:HI31").Copy
Sheets("destSheet").Range("A1:HI31").PasteSpecial (xlPasteAll)

Rodcon

Andy Pope

Hi,

If the two workbooks are open there reference names are not likely to include the path names. So this would probably work.

Workbooks("Source.xls").Sheets("sourceSheet").Range("A1:IL36").Copy
Workbooks("Target.xls").Sheets("destSheet").Range("A1:IL36").PasteSpecial (xlPasteAll)

rodcon

Same error when referencing separate files. Works if it is operating within one workbook.

Peter Mo.

Hi

This is a bit strange, because I don't get the error. What version are you running

Regards

Peter Mo.

Excel VBA: Copying from file to file

Re: Excel VBA: Copying from file to file

Re: Excel VBA: Copying from file to file

Re: Excel VBA: Copying from file to file