Adding multiple ranges to an array then using .value to copy/paste to a new sheet

0
votes

I have several named ranges (timelinemarket, clientdetailsmarket, premisesmonthlymarket, cashinflowmarket, cashoutflowmarket) that I am looping through one by one and using .value to transfer to a new sheet. This requires a big chunk of code as I am looping through each individually and doing the .value action. All of the ranges are (or are made to be) the same height (72 rows, currently).

What I want is to add all of the values in the ranges to a single array which would look something like:

range 1 col 1 range 1 col 2 range 2 col 1 range 3 col 1 range 4 col 1
value value value value value
value value value value value
value value value value value
value value value value value

Then I want to just take that array and paste it into the new worksheet at location x.

My understanding is that I could loop through the ranges one by one, add the values to the array, then use something like the below to put them into the new worksheet:

range("newrange") = myarray

The ranges to be processed will all be the same height, 72 rows, but they have different numbers of columns.

  1. Range 1: 72 rows x 2 columns
  2. Range 2: 72 rows x 1 column
  3. Range 3: 72 rows x 1 column
  4. Range 4: 72 rows x 1 column

Resulting Array: 72 rows x 5 columns

2
excelvba
Do you understand what an array is, no offence? Please, edit your question and try explaining in words what you try accomplishing. Trying to understand your code looks difficult enough. – FaneDuru
Sort of.. I have read multiple tutorials and I think I get it but then I try to actually use them and it doesn't behave in the way I'm expecting. I have edited my question to HOPEFULLY be clearer... – exoticdisease
You shouldn't delete all your code. Maybe somebody else more patient will better understand its meaning... Now, in order to give some more relevance, I think you should show us how your data to be processed looks. I know to handle arrays in a lot of ways and I like helping, but without seeing the input it is practically impossible to show you how such an output should be created... – FaneDuru
I think you were right, the code doesn't actually add anything to the question. All I need to know is how to add the ranges' values to the array... and if that is in fact a good method of achieving my desired result! And thank you for taking the time to answer, really appreciated. – exoticdisease
The simplest way is to use the range Value property. For instance arr = Range("A1:B4").value puts the range in an array having two columns and four rows. Is it a little clearer? You may test it. Then, use 'Debug.Print arr(2, 2)`. It will return the former range "B2" value... – FaneDuru

2 Answers

1
votes

Not much code to loop through the named ranges

Sub CopyRanges()

    Dim wsDashData As Worksheet
    Dim rng As Range, rngDest As Range, c As Integer
    Dim namerange, data, i As Integer
    namerange = Array("timelinemarket", "clientdetailsmarket", "premisesmonthlymarket", "cashinflowmarket", "cashoutflowmarket")
    
    Set wsDashData = ThisWorkbook.Sheets(1)
    Set rngDest = Sheet2.Range("A1")
    
    For i = 0 To UBound(namerange)
        data = wsDashData.Range(namerange(i)).Value2
        c = UBound(data, 2)
        rngDest.Resize(UBound(data), c).Value2 = data
        Set rngDest = rngDest.Offset(0, c)
    Next
    
End Sub
1
votes

In order to join two arrays in terms of columns, please use the next function:

Function JoinArrCol(arr As Variant, ar1 As Variant) As Variant
     Dim i As Long, j As Long, lastItem As Long
     
     lastItem = UBound(arr, 2)
     ReDim Preserve arr(1 To UBound(arr), 1 To UBound(arr, 2) + UBound(ar1, 2))
     
     For i = 1 To UBound(arr)
        For j = 1 To UBound(ar1, 2)
            arr(i, j + lastItem) = ar1(i, j)
        Next j
     Next i
     JoinArrCol = arr
 End Function

In order to place the ranges in arrays, please use the next Sub, which uses the above function to join the arrays. The joining result will be dropped in the next sheet:

Sub joinArraysPerColumns()
    Dim sh As Worksheet, sh1 As Worksheet, lastR As Long, arr1, arr2, arr3, arr4, arrFin
    
    Set sh = ActiveSheet
    Set sh1 = sh.Next
    lastR = sh.Range("A" & sh.rows.count).End(xlUp).row
    arr1 = sh.Range("A2:B" & lastR).value
    arr2 = sh.Range("C2:E" & lastR).value
    
    arrFin = JoinArrCol(arr1, arr2)
    sh1.Range("A2").Resize(UBound(arrFin), UBound(arrFin, 2)).value = arrFin
 End Sub