none
Excel VBA Copy cell by cell from a range from another workbook RRS feed

  • Question

  • Hi All,

    I want to copy a range ("A1:A10") from wb1 to range ("C10:19") wb2. Since my range in wb2 is a merged cells i cannot use below copy method as it unmerge the range

    range("A1:A10).COPY DESTINATION:= range("C10:C19).MERGEAREA

    How can i copy cell by cell from copy range to destination range? I don't want to use destinationrange= copyrange.

    Thanks,

    Zav

    Monday, February 29, 2016 7:33 PM

Answers

  • >>>As posted in my post, i only want to copy cell by cell. I want to use loop where each copy cell is looped. copied and than each destination cell is looped and than pasted.

    According to your description, I still don't know clearly what you mean , so please correct me if I have any misunderstandings on your question. I guess that you want this result:

    Dim rng As Range
    Set rng = Range("A1:A10")
    
    Dim dstRng As Range
    Set dstRng = Range("C10:C19")
    
    For i = 1 To rng.Cells.Count
      dstRng.Cells(i).Value = rng.Cells(i).Value
    Next


    • Edited by David_JunFeng Wednesday, March 2, 2016 8:45 AM
    • Marked as answer by zaveri cc Monday, March 7, 2016 2:53 PM
    Wednesday, March 2, 2016 8:44 AM

All replies

  • Hi,

    I'm afraid that the names of workbook and worksheet are not provided expressly in your code.
    How about this:
    Workbooks("source.xlsx").Sheets("sourceSheet").Range("A1:A10").Copy _
    Workbooks("target.xlsx").Sheets("targetSheet").Range("C10")
    Regards.
    • Edited by Ashidacchi Monday, February 29, 2016 11:26 PM
    Monday, February 29, 2016 11:24 PM
  • >>>How can i copy cell by cell from copy range to destination range? I don't want to use destinationrange= copyrange.

    According to your description, please correct me if I have any misunderstandings on your question, you could user Range.Resize Property (Excel) to resize the specified range. refer to codes below:

    Sub CopyCell()
    
       Range("A1:A10").Select
       areaRows = Range("C10").MergeArea.Rows.Count
       areaCols = Range("C10").MergeArea.Columns.Count
       
       Range("A1:A10").Resize(areaRows, areaCols).Copy
       
       Range("C10").Select
       ActiveSheet.Paste
         
    End Sub

    For more information, click here to refer about Range.Resize Property (Excel)

    In addition could you provide more information about your issue, for example screenshot, sample codes etc., that will help us resolve it.

    Thanks for your understanding.

    Tuesday, March 1, 2016 6:16 AM
  • As posted in my post, i only want to copy cell by cell. I want to use loop where each copy cell is looped. copied and than each destination cell is looped and than pasted.
    Tuesday, March 1, 2016 7:18 PM
  • >>>As posted in my post, i only want to copy cell by cell. I want to use loop where each copy cell is looped. copied and than each destination cell is looped and than pasted.

    According to your description, I still don't know clearly what you mean , so please correct me if I have any misunderstandings on your question. I guess that you want this result:

    Dim rng As Range
    Set rng = Range("A1:A10")
    
    Dim dstRng As Range
    Set dstRng = Range("C10:C19")
    
    For i = 1 To rng.Cells.Count
      dstRng.Cells(i).Value = rng.Cells(i).Value
    Next


    • Edited by David_JunFeng Wednesday, March 2, 2016 8:45 AM
    • Marked as answer by zaveri cc Monday, March 7, 2016 2:53 PM
    Wednesday, March 2, 2016 8:44 AM
  • That's exactly what i need. Thanks.
    Monday, March 7, 2016 2:53 PM