CODEWITHSUNDEEP
excelvba
Tom M.
Tom M.

Reputation: 149

moving range of values from one sheet to another

I have a range of distinct integer values, let's say from d7:o7 . I'd like to move these values to another location in the same work book (based on logic outside the scope of this question). What is the best way to do this?

My initial (failed) approach went a little like this:

create a variant

Dim myArray As Variant

assign values to array

myArray = Range("d7:o7")

output array of integers in a new range

Range("d10:o10") = myArray

Thanks in advance!

Upvotes: 0

Views: 795

Answers (3)

bonCodigo
bonCodigo

Reputation: 14361

I beleive the best answer is given above :) where you have accepted.

So I am posting this, in order to throw some perspective to what you started doing. You may do this for Transposing data from ranges to arrays and vise versa. However when outputing it depends if you are using a multi-dimensional array or single-D array. We take Sheet1 for the sample code.

Dim myArray As Variant
'--to array
myArray = WorksheetFunction.Transpose(WorksheetFunction_ 
            .Transpose(Sheets(2).Range("D7:O7").Value))
'--to sheet
Sheets(1).Range("D10").Resize(1, _ 
            UBound(Application.Transpose(myarray))) = myarray

I have used a double transpose to keep a multi-column from creating a 2D array. So it will look like the following:

enter image description here

Upvotes: 0

John Bustos
John Bustos

Reputation: 19544

Range("D10:O10").Value = Range("d7:O7").Value

Hope this helps

Upvotes: 3

A B
A B

Reputation: 4148

This will copy (not delete the original values), assuming you are staying on the same sheet, but the column is represented as a number (E.G. A=1, B=2 etc.). This example is for a 2 by 2 array, but you can easily change the start and end row and column numbers:

Sub CopyCells()

    Dim SrcBeginRowNum As Integer
    Dim SrcBeginColNum As Integer
    Dim SrcEndRowNum As Integer
    Dim SrcEndColNum As Integer

    Dim DstBeginRowNum As Integer
    Dim DstBeginColNum As Integer


    Dim ri As Integer
    Dim ci As Integer

    Dim dest_ri As Integer
    SrcBeginRowNum = 1
    SrcBeginColNum = 1
    SrcEndRowNum = 2
    SrcEndColNum = 2


    DstBeginRowNum = 34
    DstBeginColNum = 5

    dest_ri = DstBeginRowNum
    dest_ci = DstBeginColNum

    For ri = SrcBeginRowNum To SrcEndRowNum

        dest_ci = DstBeginColNum

        For ci = SrcBeginColNum To SrcEndColNum

            Cells(dest_ri, dest_ci).Value = Cells(ri, ci).Value
            dest_ci = dest_ci + 1

        Next
        dest_ri = dest_ri + 1


    Next



End Sub

Upvotes: 0

Related Questions