none
Copy a Range of cells from one workbook to another RRS feed

  • Question

  • I am trying to copy a range of cells from one worksheet to another using the Cells() reference.

    The following two lines of code work fine.
    Cells(1, 1).Value = Worksheets(T).Cells(2, 1).Value
    Range(Cells(1, 1), Cells(1, 2)).Value = Range(Cells(2, 1), Cells(2, 2)).Value

    This line errors out on me with a run time error 1004
    Range(Cells(1, 1), Cells(1, 2)).Value = Worksheets(T).Range(Cells(2, 1), Cells(2, 2)).Value

    I am sure it is just a syntax thing but I can not get it to work. Any help would be appreciated.

    Tuesday, April 14, 2015 11:12 PM

Answers

  • Hi James

    >> I would also like to avoid using the clipboard.

    You can using the following code to copy the values without clipboard.

    Worksheets("Sheet2").Range("A5:B5").Value = Worksheets("Sheet1").Range("A5:B5").Value

    Best Regards

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Monday, April 27, 2015 11:51 PM
    Wednesday, April 22, 2015 10:02 AM
    Moderator

All replies

  • Re:  copy a range

    Cells refers to the active sheet unless you specify otherwise...
    (note the dots used below)
    '---
      With Worksheets(T)
        Range(Cells(1, 1), Cells(1, 2)).Value = .Range(.Cells(2, 1), .Cells(2, 2)).Value
      End With
    '---
    If you are using more than one sheet, it is good practice to specify the sheet for all range references.

    ---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Monday, October 31, 2016 11:12 PM
    Wednesday, April 15, 2015 12:08 AM
  • Sub foo()
    Dim x As Workbook
    Dim y As Workbook

    '## Open both workbooks first:
    Set x = Workbooks.Open(" path to copying book ")
    Set y = Workbooks.Open(" path to destination book ")

    'Now, transfer values from x to y:
    y.Sheets("sheetname").Range("A1").Value = x.Sheets("name of copying sheet").Range("A1")

    'Close x:
    x.Close

    End Sub

    -----------------------------------------

    Free .NET Barcode Generator & Scanner supporting over 40 kinds of 1D & 2D symbologies.

    Wednesday, April 15, 2015 7:17 AM
  • Thanks for the replies.

    @James Cone -

    Your code only looks at the one worksheet. I need to copy from one worksheet to another.

    @Ashok Sh

    I am only using one workbook (two worksheets). Your solution uses the "A1" notation. I know how to make it work using that notation. I want to use the notation Cells(1,1) with a range on specified worksheet.

    Thanks,
    Scott

    Wednesday, April 15, 2015 10:12 PM
  • Hi,

    >>Copy a Range of cells from one workbook to another

    When we copy a range from a sheet to another sheet, we need to select the worksheet that needs to be copied to  . You could use the following code to copy a range from a sheet to another worksheet.

    Sub CopyRange()
    
    Worksheets(1).Select
    
    Range(Cells(1, 1), Cells(1, 2)).Select
    
    
    Selection.Copy
    
    Worksheets(2).Select
    
    Worksheets(2).Range(Cells(1, 1), Cells(1, 2)).Select
     Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
             
    ActiveSheet.Paste
    
    End Sub
    
    

    Hope this could help you.

    Best Regards

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, April 16, 2015 10:00 AM
    Moderator
  • I appreciate your time with me on this.

    I would like to avoid using "select" as it will slow down the code.

    I would also like to avoid using the clipboard since I think it will slow down my code and I will have to be careful not to copy anything manually while my code is running. My code will probably take several minutes to run and I may be doing other things on the computer while it is running.

    Thank you,

    Thursday, April 16, 2015 2:52 PM
  • Scott,
    RE:  "Your code only looks at the one worksheet"

    Only if worksheets(T) is the active sheet.

    '---
    Jim Cone
    • Edited by James Cone Monday, October 31, 2016 11:12 PM
    Friday, April 17, 2015 2:57 AM
  • Hi James

    >> I would also like to avoid using the clipboard.

    You can using the following code to copy the values without clipboard.

    Worksheets("Sheet2").Range("A5:B5").Value = Worksheets("Sheet1").Range("A5:B5").Value

    Best Regards

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Monday, April 27, 2015 11:51 PM
    Wednesday, April 22, 2015 10:02 AM
    Moderator