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
 

szita2000

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

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,939
Messages
5,621,720
Members
415,853
Latest member
Newlife72

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