Excel VBA: Use Dynamic Arrays to Transfer Data and Build Dynamic Reports
HTML-код
- Опубликовано: 22 июл 2024
- We will build a macro where the end-user of the file can control how the columns from the source data need to be copied to our destination worksheet. They will be able to choose which columns to copy over and in what sequence.
The macro will also, feature a vlookup style method of getting adjacent data from another worksheet based on a certain value in the current worksheet.
For this video, we will only use dynamic arrays. Note: Dictionaries can be used to further optimize the code and we will use them in another video.
Using dynamic arrays is much faster compared to looping over a data range on a worksheet.
All the code will be built from scratch. It is available on my blogsite. Link below.
skillsandautomation.com/usefu...
You can download the macro file from Github:
github.com/skillsandautomatio... Наука
You took a complicated topic, and made it super easy to understand. THANK YOU!
One of the most usefull video I have ever seen. Thank you.
Very nicely explained about array 👌👌👌👌👌👌
This is an amazing work, thank you so much
This is super useful. I have subscribed to your channel.
Harika bir sunum. Teşekkürler.
Amazing job brother
Great. Love it. Can you please prepare the same example using dictionaries?
Super helpful! do you have a reference when the IN sheet header is on a different row? Example row 7 is the start of the header
Hi, Do you mean you want to dynamically search for the starting header row? i.e. could be any row 2,3 etc.? i would suggest using the find method. i have a separate video that shows how to dynamically identify any dataset no matter where its placed in the sheet. code can be found here: skillsandautomation.com/how-to/how-to-find-last-row-in-excel-vba-using-find-method/#Example_1_Identify_and_Select_Data_Range
thanks
hi there, epic lecture !! feedback:
Group array is not woking properly: these appear unmapped .....
Caramelised Onion Relish 400G x 10
Crunchy Peanut Butter 500G x 10
Spicy Mango Chutney 400G x 10
Hi, Sorry for the super late reply. I was away from the channel for over a year in between. This comment must have slipped through. Still thought I will reply for the benefit of others though. I have tried out the code again, and these 3 product codes map correctly for me. e.g. Caramelised Onion Relish 400G x 10 is mapped in the Output Sheet to Condiments & Relish. i.e. If you run the code from the downloaded macro file as-is, no product should be unmapped. If this is not true for you or any other viewer, please confirm. If you have perhaps changed some values anywhere else, and the product codes are unmapped as a result of that, please confirm. My long shot guess is that Excel is acting funny, rather than the code itself. But, i would need more details to know either way.
Nice Explained Please Zoom the code
Plz share practice file
Hi, I've uploaded the file to Github now. github.com/skillsandautomation/dynamic-arrays-transfer-data