none
Subscript error (9) when copying from one wk book to another RRS feed

  • Question

  • I have trouble with trying to copy content from one sheet in a source workbook (called here cwbk - and this source workbook must be dynamic as it's reference name changes regularly) to a newly created workbook with a constant name. With the code below, I get a 'destination subscript out of range' error message.

    Can anyone enlighten me as to where I am going wrong please?

    Dim cwbk As Workbook
    Dim iFaceFile As Workbook

    Set iFaceFile = Workbooks.Add
    iFaceFile.SaveAs Filename:="E:\Documents\iFaceFile.xls"

    cwbk.Activate

    Sheets("Sheet2").Columns("A:M").Copy Destination:=Workbooks("iFaceFile").Sheets("Sheet1").Range("A:M")

    Tuesday, August 14, 2012 3:19 PM

All replies

  • You have not set the cwbk.

    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Tuesday, August 14, 2012 3:31 PM
    Answerer
  • Try something like:

    Dim cwbk As Workbook
    Dim iFaceFile As Workbook
    
    Set iFaceFile = Workbooks.Add
    iFaceFile.SaveAs Filename:="E:\Documents\iFaceFile.xls"
    set cwbk=ActiveWorkbook
    
    cwbk.WorkSheets("Sheet2").range("A1").CurrentRegion.Copy Destination:=iFaceFile.WorkSheets("Sheet1").Range("A1")

    The Worksheet names must work. CurrentRegion copies everything until a blank row and column is reached.


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Tuesday, August 14, 2012 9:25 PM