Create Excel Workbooks Worksheets Automatically with Excel VBA Arrays

Discover how to create a custom Excel workbook using VBA in our step-by-step tutorial. Perfect for beginners and intermediate users, this video guides you through the process of making a workbook with a separate worksheet for each company listed in your Excel sheet.

Learn how to:
▪️ Use arrays for efficient data handling.
▪️ Loop through rows and columns to manipulate data.
▪️ Create a new workbook with individual sheets for each company.
▪️ Populate sheets with company and manager names.

🔑 Key Features:
▪️ Easy to Follow: Clear, step-by-step instructions.
▪️ Practical Use of Arrays: Learn to use two-dimensional arrays in Excel VBA.
▪️ Workbook Customization: Automatically create a new workbook with specified data.
▪️ Real-World Example: Apply these techniques to manage company data efficiently.

★ Links to related videos: ★

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#ExcelVBA

[stuartboyle]

Leila Nice Job! You are the only video that pops up which is truly working with an Array and not confusing a list of cells for a declared Array.

[ExcelForFreelancers]

Thanks for sharing your knowledge on arrays. Nice job Leila.

[quasipseudo]

You explain things well, instead of simply showing off your knowledge. Very good.

[sawkyawhtike]

The best tutorial on array that I have ever seen. You are great.

[ckokse]

Thanks for that! Great example and very well explained.

[HerculesSantosLoyola]

Amei sua aula, você explica muito bem. Tem poucas aulas em português sobre esse conteúdo. Obrigo pela ajuda.

[SolidSnake]

Hi, I have learned from Paul Kelly that assigning array is much simpler. You can just use such a code:


dim arr as Variant


arr = Range("A6").CurrentRegion


And that's it!

[ian_senior]

thanks Leila, extremely helpful and easy to understand. You are a great Instructor and beautiful lady. :)

[petralabui]

best youtube chanel for excel, simple, easy and right to the point

[wynhopkins]

Well explained on something that can be really tricky

[pinkeishz]

It was great example of 2 dimensional Array, which was quick well explained... 👌

[mrashid]

Thank you for the lesson. This teaches how to create 2D arrays. Once learnt, one may try this alternative mean for copying the data from sheet to array(dynamic ). This eliminates declaring some variables, using 2 nested loops and rewriting codes when number of company/manager increases.

Dim Compinfo As Variant
' To be used as dynamic array
Dim i as Integer

' Following code will create 2D array in Compinfo variable taking the values from range A6:B14
Compinfo =

For i = 2 to UBound(Compinfo, 1)
' Compinfo(i, 1), Compinfo(i, 2)
Next i

[rbd]

Excellent teacher! I also take your courses on Udemy. Well done

[jaredwhitener]

Helpful video. I added code at the end of the line for adding a new sheet. "Set ShNew = This forces the tabs/sheets to be in order. Top to bottom on the list becomes left to right in the sheet order. Also, a line at the end could be added, "Sheets(1).Delete", to get rid of the blank sheet (given you adopted my above code addition for order).

[abdulazizal-jasser]

You can copy the range to the array in one line instead of the loop: CompInfo = Range("A7:B14").Value. Before that you need to declare the array: Dim CompInfo As Variant

[ahrorkuldashev]

Struggling to understand what kind person would give a dislike to this video.

[ashoksahu]

Very very nice video. Very well explained

[BillSzysz]

Thanks Leila for a very clear explanations of building and using arrays in VBA:-))
I know that this is VBA lesson but I would like to throw in my two cents here. This particular case we can do without VBA. I think, in 1 - 1.5 minute :-)) And it doesn't matter how many worksheets we need to add.

[jaaptijssen]

Nice video with clear explanation. about creating Array!

What exactly are the benefits of using an array.?
This is also possible with a loop based on the cell values ​​in Excel. (I am novice... :))


So hence the question, am a novice VBA so I am interested.

[RajaTRemyaR]

Good one. You can use dictionary in VBA which is more useful than array