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

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
76
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,732
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2007
Messages
1,092
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows
If you do have a solid naming convention I would suggest you use a dictionary where the values for the keys are arrays.
 

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
76
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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. :biggrin::biggrin::biggrin:

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


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

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows
What do you need to do with this data?
 

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
76
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
1604493056654.png


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...

1604493148964.png


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.


1604493286602.png
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows
Where do Molding, NGC6 etc from the first sheet come into it?
 

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
76
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 25, 2012
Messages
76
Office Version
  1. 365
Platform
  1. Windows
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,597
Messages
5,625,724
Members
416,130
Latest member
galgozzi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top