Consulting

Results 1 to 3 of 3

Thread: Solved: copy range with formula unchanged

  1. #1
    VBAX Newbie
    Joined
    Oct 2008
    Posts
    3
    Location

    Solved: copy range with formula unchanged

    Hi,

    I've been searching internet for quite a long time without luck, hope somebody can shed some light here.

    I have two workbooks, let's say A and B, I need to copy a range R1 in Sheet1 of workbook A to Sheet1 of workbook B. The code I use like this:

    Set source = Workbooks("C:\tmp\A.xls").Sheets("Sheet1").Range("A1:A10")
    Set dest = Workbooks("C:\tmp\B.xls").Sheets("Sheet1").Range("A1")
    source.copy dest

    The problem I have is some of the cells in source have reference like this:

    =OFFSET(Sheet2!A1,0,1,1,1)

    After copy, in Sheet1 of B it becomes:

    =OFFSET([A.xls]Sheet2!A1,0,1,1,1)

    How can I keep the original OFFSET unchanged?

    Thanks in advance!

    Jack

  2. #2
    You may want to try doing:

    source.copy dest
    dest.formula = source.formula

  3. #3
    VBAX Newbie
    Joined
    Oct 2008
    Posts
    3
    Location
    Got it, thank you very much. The code I use like this:

    Sub copy1()
        Set sbook = Workbooks.Open("C:\tmp\aaa.xls")
        Set dbook = Workbooks.Open("C:\tmp\bbb.xls")
        
        Windows("bbb.xls").Activate
        Sheets("Sheet1").Activate
        Set source = Range("A1:A10")
        
        Windows("aaa.xls").Activate
        Sheets("Sheet1").Activate
        Set dest = Range("A1:A10")
           
        dest.Value = source.Formula
        
    
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •