Copy Ranges between sheets with Cycles

[AlexSea]

Im searching for solution on how to populate blocks 1,2,3,4,7,9 with values from cache sheet (SQL query) where ranges change vertically. I'm stuck on this problem for a long time.

Until cache sheet columns vertically changes (up/down) , i can't just use straight range copy/paste methods. I believe there's a great solution with Cycles
More details in attachment NeedCycle.xlsx

[mdmackillop]

Using SUMPRODUCT

[AlexSea]

Thank you. That's good but not for me. i need using cycles over the macro, cause i need to keep sheet CALC clear.

[Tom Jones]

It's not fair to ask same question to many sites

https://www.ozgrid.com/forum/forum/h...ro-over-cycles

[mdmackillop]

I'll leave you to tidy up the formatting

Sub Test()
    Set wss = Sheets("CALCcache")
    Set wst = Sheets("CALC")
    arr = Array(1, 2, 3, 4, 7, 9)
    With wss
        For a = 0 To 2
            Set strt = .Range("A:A").Cells.Find(arr(a), after:=.Cells(Rows.Count, 1), searchdirection:=xlNext)
            Set endd = .Range("A:A").Cells.Find(arr(a), after:=.Cells(Rows.Count, 1), searchdirection:=xlPrevious)
            rws = 2 + endd.Row - strt.Row
            Set tgt = wst.Cells(1, 1).Offset(, 7 * a)
            Range(strt, endd).Resize(, 6).Copy tgt
        Next a


        For a = 3 To 5
            Set strt = .Range("A:A").Cells.Find(arr(a), after:=.Cells(Rows.Count, 1), searchdirection:=xlNext)
            Set endd = .Range("A:A").Cells.Find(arr(a), after:=.Cells(Rows.Count, 1), searchdirection:=xlPrevious)
            Set tgt = wst.Cells(1, 1).Offset(rws, 7 * (a - 3))
            Range(strt, endd).Resize(, 6).Copy tgt
        Next a
    End With
End Sub

[AlexSea]

Impressive. Thank you very much mdmackillop. Could you please exclude ID and Date from result set after macro is done.

it works fast and great, and i want to figure out how to change result location of the on the sheet. For example: I need to locate blocks 1, 2, 3, 4 straight over 1 column. Block 9 under block 1 and block 7 under block 4?

[AlexSea]

I have figured out with location. I played with .offset and got it Thank you very much.
Now my problem is definitely SOLVED

[mdmackillop]

I played with .offset and got it

Best way to learn! You can mark this solved using Thread Tools.