Copy multiple range from one sheet to another

    Hi,


    I've been trying to copy multiple cells from sheet 1 to sheet 3. But every time that I will add a cell on the range it is giving me an error message "That Command cannot be used on multiple selections."
    There are also other cell that I want to copy and paste it on a specific cell in sheet3
    for example
    Sheet1: b4 and b6 I need to paste it in sheet3 cell is E4 and B4


    so the B4 in sheet 1 should be pasted in sheet3 E4 and b6 should be pasted in B4.



    Try this. Add as many ranges as required. Change references as required.

    Code
    1. Sub AAAA()
    2. Dim cArr, i As Long, sh3 As Worksheet
    3. Set sh3 = Sheets("Sheet3")
    4. cArr = Array("D10:D12", "D15", "D25", "D32:D33") '<-----Add ranges as required
    5. For i = LBound(cArr) To UBound(cArr)
    6. Range(cArr(i)).Copy sh3.Cells(Rows.Count, 10).End(xlUp).Offset(1)
    7. Next i
    8. End Sub


    BTW, this works also



    Code
    1. Sub BBBB()
    2. Range("D10:D12,D15,D22,D25,D32:D33").Copy '<----- Add ranges as required
    3. Sheets("Sheet3").Cells(Rows.Count, 9).End(xlUp).Offset(1).PasteSpecial Paste:=xlValues
    4. End Sub



    Google about "selecting" in excel. You should stay away from it as much as possible, which is just about 100 % of the time.

    BTW, this works also


    Code
    1. Sub BBBB()
    2. Range("D10:D12,D15,D22,D25,D32:D33").Copy '<----- Add ranges as required
    3. Sheets("Sheet3").Cells(Rows.Count, 9).End(xlUp).Offset(1).PasteSpecial Paste:=xlValues
    4. End Sub


    Google about "selecting" in excel. You should stay away from it as much as possible, which is just about 100 % of the time.

    Code
    1. Sub This_Maybe()
    2. Dim rngArr
    3. rngArr = Array([D10], [D11], [D12], [D15], [D22], [D25], [D32], [D33], [D38], [D39], _
    4. [D40], [D41], [D42], [D47], [D48], [D49], [D50], [D53], [D55], [D57], [D63], [G3])
    5. Sheets("Sheet3").Cells(Rows.Count, 9).End(xlUp).Offset(1).Resize(, UBound(rngArr) + 1) = rngArr
    6. End Sub
    Quote from jolivanes
    Code
    1. Sub This_Maybe()
    2. Dim rngArr
    3. rngArr = Array([D10], [D11], [D12], [D15], [D22], [D25], [D32], [D33], [D38], [D39], _
    4. [D40], [D41], [D42], [D47], [D48], [D49], [D50], [D53], [D55], [D57], [D63], [G3])
    5. Sheets("Sheet3").Cells(Rows.Count, 9).End(xlUp).Offset(1).Resize(, UBound(rngArr) + 1) = rngArr
    6. End Sub



    I'm currently not infront of my work computer right now. be there in 15mins. i'll try this one out. I'll update you what happened. thanks :)

    Quote from jolivanes
    Code
    1. Sub This_Maybe()
    2. Dim rngArr
    3. rngArr = Array([D10], [D11], [D12], [D15], [D22], [D25], [D32], [D33], [D38], [D39], _
    4. [D40], [D41], [D42], [D47], [D48], [D49], [D50], [D53], [D55], [D57], [D63], [G3])
    5. Sheets("Sheet3").Cells(Rows.Count, 9).End(xlUp).Offset(1).Resize(, UBound(rngArr) + 1) = rngArr
    6. End Sub


    Hi,
    This works perfectly. one last question sir. Example is I want to copy b4 in sheet one together with all the cells i have and then paste it on sheet 3 which is in E and then right after that the D10 will be pasted in I. I mean is there a way to skip 3-4 cells?

    Quote from marav08

    Hi,
    This works perfectly. one last question sir. Example is I want to copy b4 in sheet one together with all the cells i have and then paste it on sheet 3 which is in E and then right after that the D10 will be pasted in I. I mean is there a way to skip 3-4 cells?


    Oh after I deleted the data in sheet3 to try it again. the code doesn't seem to work. :(

    Which sheet was showing when you ran the code the 2nd time? The sheet with all the cells to be copied has to be the active (showing on your desktop) sheet.
    If, for instance you copy from sheet1 to sheet3, you have to have sheet 1 as active sheet.
    If you want to run it from anywhere, let us know.