Best way to copy ranges from different sheets and store them into an array

Question

I am trying to copy ranges from different sheets and store them in an array. what is the best way ? I know that UNION doesn't work from different sheets so here is what I am doing, it's working but I would like to know if there is a better way. thanks in advance

    Dim MyArray As Variant

Worksheets("Sheet1").Range("A1:A" & Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row).Copy Worksheets("main").Range("A1")
Worksheets("Sheet2").Range("B1:B" & Worksheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row).Copy Worksheets("main").Range("A11")
Worksheets("Sheet3").Range("C1:C" & Worksheets("Sheet3").Cells(Rows.Count, 3).End(xlUp).Row).Copy Worksheets("main").Range("A15")

    MyArray = Worksheets("main").Range("A1:A20").Value

Are you looking to produce a 2-D, one-based array or a 1-D, zero-based array? The above is the former but could easily be changed to the latter. — , Jan 27 '19 at 00:10
What are you planning to do with the array? You could copy all ranges into an Array of Arrays (3D array, jagged array). — VBasic2008, Jan 27 '19 at 01:21
Can you explain more what do you mean with your suggestions, I didn't understand what is the difference between if I want 1D 2D or 3D arrays...what I want is to pass it to a function — JustGreat, Jan 27 '19 at 01:55

Variatus · Accepted Answer · 2019-01-28T02:24:57.113

0

It looks like you misunderstood PEH's intention. I am explaining and expanding on it. Here is a pictue of the worksheet he recommended you to cache.

You want to cache the range B2:D7 (or E7 in the future). Here is the code that does the caching.

Sub GetTemplateAddresses(AddList As Variant)
    ' loads AddList only if not already loaded

    Dim Rng As Range                ' Range of cached addresses table
    Dim Rl As Long                  ' last used row in Ws "Cache"

    If IsEmpty(AddList) Then                    ' skip if AddList is already set
        With Worksheets("Cache")                ' use your own name for this worksheet
            Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
            ' start from B2 to (LastRow of Template3)
            Set Rng = .Range(.Cells(2, 2), .Cells(Rl, Ncc3 + 1))
            AddList = Rng.Value
        End With
    End If
End Sub

Before you can call this sub you need to prepare a little. Observe that the sub has a parameter, AddList. The idea (courtesy @PEH) is that you don't need to cache it again if it's already cached. So, you supply the array variable and the code loads it only if it is blank.

The other preparation is the reference in the above code to enumeration Ncc3. The Enum is designed to enable easy addressing of the entire AddList array. Mind that it must be at the top of any standard code module, before any procedure on that sheet. Here is the full enumeration. Option Explicit

Enum Ncc                            ' Array of Cached addresses
    Ncc1 = 1                        ' Template 1
    Ncc2
    Ncc3
    NccFname = 1                    ' assign integers only
    NccMname                        ' if no value is assigned,
    NccLname                        ' VBA assigns previous + 1
    NccCity
    NccStreet
    NccZip
    NccTel
    NccEmail                        ' NccEmail = 8 (test with ? NccEmail in the Immediate window)
End Enum

I have designed a little testing procedure for you. Install it in a standard code module, why not below the above enumeration and above the sub it calls?

Private Sub TestGetAdd()
    Dim AddList As Variant
    Dim TemplateNumber As Ncc, FieldName As Ncc

    GetTemplateAddresses AddList
    TemplateNumber = Ncc2                   ' change the numbers for testing
    FieldName = NccStreet                   ' the press F5
    MsgBox "You have entered template No. " & TemplateNumber & vbCr & _
           "and specified field No. " & FieldName & String(2, vbCr) & _
           "The address is " & AddList(FieldName, TemplateNumber)
End Sub

It is ready to run. Test it.

To use this whole setup you can now proceed to define cells from any of your templates after you know the worksheet's name and its template number. Ws.Range(AddList(NccCity, Ncc2)).Value will return the name of the city from template 2 according to the address for that field stored in the cache.

edited Jan 28 '19 at 02:24

answered Jan 27 '19 at 03:46

Variatus

14,293
2
14
30

Think you wanted redefine your target sheet ("main") in OP :-) BTW In VBA I prefer type declarations `As Long` to e.g. `Dim i As Integer` - @Variatus – T.M. Jan 27 '19 at 09:44
1

@T.M. Yes, indeed. Thank you. I have corrected the code. – Variatus Jan 27 '19 at 10:09
thanks to both of you, maybe I should have been more clear about my needs, if you check this post you will understand why I need the array : https://stackoverflow.com/questions/54329409/vba-excel-getting-same-data-from-different-sheets-at-different-positions/54330273 I didn't want to mix both subjects because filling arrays is independant and if one day someone want to use, he can refere to this thread without looking at the other one which is very specific – JustGreat Jan 27 '19 at 23:37
@Variatus thanks a lot for your efforts,I will check later on your code, cuz I am now behing a proxy at work that prevent me from seeing the images you attached Please check back the answer of PEH, maybe it's you who missundersood not me, because the picture on his answer is very clear, check 1- in his answer As for caching,his idea is great to cache,which makes code faster, and he used public variable to cache the arrays.I don't see in your code any public variable,I will read it and try to understand it before I comment about. during this time plz check again the answer of peh.thanks a lot – JustGreat Jan 28 '19 at 11:00
I showed you how to assign values to the array without deciding where you keep it. Making it public may appeal to you but public arrays of large size eat up large chunks of public memory. In 9 cases out of 10 public variables can be avoided by proper code design. Just declare it where you need it and use it where you declared it. I would say for a large array like this one (more than 300 values) some effort should be made to avoid public declaration. – Variatus Jan 28 '19 at 11:42
And without beeing public il Will cache data? Did you check back the answer of peu and see the différence between your lookup data sheet and his one? Yours looks like more like mine not like the one suggested by peh. By the way any advantage of storying the location of cells by A1 B1 ETC..instead of making it like i wanted storing the row and column like row=1 col =1 instead of A1? – JustGreat Jan 28 '19 at 12:04
As I said, I expanded on PEH's suggestion, didn't copy it. You found one difference: I suggest to use A1 addresses which require only one column per template. The key point I wanted to address is that PEH did, it seems to me, think of a single sheet, perhaps same as you did. – Variatus Jan 29 '19 at 02:06
@Variatus thanks a lot,I just read and understood your code. the main difference between you and peh, is that your not using the find method, you are using the enum to store implicitly the row number of each field and sheet number of templates.The rest is almost the same, but the way he is using cache is based on public array, while yours is based on the developper who should manage to cal only once the procedure GetTemplateAddresses...As for the content, I think it's better to use 2 columns Rows and Col instead of A1 etc.cuz I may need this information later in my code and to avoid conversion – JustGreat Jan 29 '19 at 14:22
@Variatus can you confirm what I just said above please? And tell me please, yours and Peh's suggestion, will give almost same result, any idea which is more performant when I have about 3 templates, each with 70-100 fields ? – JustGreat Jan 29 '19 at 14:25
Having row and column separated into two values will run faster but will be slower to read, perhaps advantage PEH by 1/10 second. More importantly, it will also require an extra dimension for the array, which means more voluminous code to access. On balance, I believe my suggestion to be an improvement. You can always extract row and column from a range, like `Range("C19").Row` and `Range("C10").Column`. Whether to keep the cache (array variable) private or make it public is a matter of code layout, not project design. – Variatus Jan 30 '19 at 02:52
@Variatus thanks again, can you explain to me why rows/col runs faster than range ? any idea about the reason ? Anyway I will evaluate the rest of the code if I need to work on rows and colums later in my code, I will use the way of rows/colums, if not I will use the range and will use as you say range("C19").Row to get the value. will see that, thanks again, you and Peh teached me too many things in your answers. thanks to both of you. Btw, I think that using enum is gonna be faster than using the Find suggested by Peh. am I right ? anyway I will tagg it as answered, thanks again – JustGreat Jan 30 '19 at 09:30
Excel defines cells by row and column numbers. If you provide an A1 notation will have to re resolved. If you provide row and column there is no need to resolve but there are two lookups instead of one. `Find` is a solution I try to avoid. I don't think @PEH was serious about that. As he did, I also try to offer a solution (enums) which won't be necessary. Your best solution is to use neither. As all results are written into one row, just place the source addresses in the sequence of the target columns and pull the data in a loop. – Variatus Jan 30 '19 at 09:39
I am sorry he used Match not Find. Dunno if it changes a lot. I am not sure about understanding well what do you mean by that, "just place the source addresses in the sequence of the target columns and pull the data in a loop", but if I understand it well, this mean my code won't be flexible at all, I have to reorder things if one day the order of fields changed – JustGreat Jan 30 '19 at 10:23
Our discussion no longer relates to your question. Let's cut it short. I believe you want to import data from various templates into a single sheet, one dataset to go into one row. The columns of that row are always the same. No flexibility needed. The source addresses of the data is different depending upon template. This needs flexibility. So, if you number your target columns 1 and up (column A and up) you can associate a source cell in another sheet with each column, f.i A=C5, B=D17,C=Y122 - whatever, but the target is in sequence, like column 1, 2 3 – Variatus Jan 30 '19 at 10:33

Best way to copy ranges from different sheets and store them into an array

1 Answers1

Linked