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

 

[offthelip]

The obvious way to do that is to use variant arrays. This works very well if the main area of your destination workbook just holds data and doesn't have equation scattered all over it.
If this is the case ,then you just load the entire source worksheet into a varaint array, then go to the destination workbook, load the entire destination workbook into another array, then do a double loop looping through the source array to find the data and the loop through the destination array to find where it goes. when the two loops are fininshed you writethe destination array back to the destination worksheet. This will be extremely fast because the all the processing is done in memory.

 

[jkpieterse]

Perhaps Powerquery might do this job too, given that you are using find I assume there is a sort of index column in both tables you can use to join the two

 

[Norie]

If you do have a solid naming convention I would suggest you use a dictionary where the values for the keys are arrays.

 

[szita2000]

Thanks for the suggestions.

I wish you guys could see what I have to work with.
Your typical - manager created,- "I don't want to do it any other way" scenario.

This is the "structure" of the source file.

I think for making unique values/names I will concatenate the lines: (Molding) and the KPIs (PR, UPDT etc...)

I think I will try to do something that @offthelip suggested.
Probably with the help of a helper column that will be the concatenated line and KPI values. As for the horizontal loop I can use the months.

 

[Norie]

What do you need to do with this data?

 

[szita2000]

What do you need to do with this data?

Hi Norie.

I need to grab PR, UPDT and Stop KPI-s and stick them in to another workbook which looks like below:

This is a closeup for one of the lines VPL1.
The purple cells are the ones I need to feed with the corresponding values from the other workbook that I posted above.

BUT

The sheet where these tables live looks like this:

Super inconvenient format. (Not mine)
I started to organise them in to a single table that looks like the source for a Pivot table (see below)

These tables feeding data to multiple charts (I don't know why management is afraid to use slicers and pivot chart???)
Instead we have 36 different Bar charts in 3 tabs...

So right now I am here:
Where I added in Column A:B The line name will be in column A, the month will be in column B.

 

[Norie]

Where do Molding, NGC6 etc from the first sheet come into it?

 

[szita2000]

Where do Molding, NGC6 etc from the first sheet come into it?

If you look at post #5 The line names in the first column.
That's in the source workbook.
All of I shown in #7 are the destination wb.

 

[szita2000]

If you look at post #5 The line names in the first column.
That's in the source workbook.
All of I shown in #7 are the destination wb.

In the original format of the destination wb the names are in a merged cell above the table.
Shown in Post #7 first image.
A bit of a nightmare.