PDA

View Full Version : Copy Ranges between sheets with Cycles



AlexSea
11-07-2017, 12:49 PM
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 20880

mdmackillop
11-07-2017, 03:55 PM
Using SUMPRODUCT

AlexSea
11-07-2017, 09:41 PM
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
11-08-2017, 01:38 AM
It's not fair to ask same question to many sites

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1195026-copy-ranges-from-one-sheet-to-another-with-macro-over-cycles

mdmackillop
11-08-2017, 02:45 AM
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
11-08-2017, 01:09 PM
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
11-09-2017, 06:13 AM
I have figured out with location. I played with .offset and got it Thank you very much.
Now my problem is definitely SOLVED

mdmackillop
11-09-2017, 06:53 AM
I played with .offset and got it
Best way to learn! You can mark this solved using Thread Tools.