Excel VBA copy-find-paste. Use of collections or Dictionaries?

[szita2000]

Hi All.

Hope you all safe and good in these Covid times.

I have a task ahead of me and I am thinking that maybe there is a better way to code this.

I have two workbooks. Source and target. I need to pull out values from the source and paste it to the target,

It is not a block per say, but rather a looping operation. Loop down in source to find the value needs to be copied then range.find in the target workbook to find where it needs to be pasted,
I can build this.

My question:

1.Is there a better way to do this?
I couldn't get my head around Collections and Dictionaries, but I thought that if I could loop over the source data first and collect ALL of the values of the different KPI-s with a solid naming convention.
Then go over to the target workbook and loop it out from the collection.

2. Is this possible? to hold a full "result table" from looping and collecting all the values/KPIs I need?

3.Would there be any advantage to using this holder collection?
In both ways I would have to loop in both workbooks. I think that the only advantage I would have is not having to flip between each loop when copy and paste. (Or use x.value = y.value method)

I will start to build the way I can code it now. Which is loop-copy, then find and paste.

Any suggestion is welcome.

Thanks

 

[szita2000]

Nevermind guys. Thanks for the help!
I will organise the destination workbook nightmare in to a Pivot table then I can churn out pivot charts for them.
That way the update will be easier.
Anyhow
Thanks for the help!

 

[Norie]

I think that it's possible to do something with dictionaries and arrays but unfortunately I don't have time to look at it right now.

If I do I'll try an post something.