Create Excel Workbooks Worksheets Automatically with Excel VBA Arrays
Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
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.
🎓 Get access to the full Excel VBA course here 👉 www.xelplus.com/course/excel-...
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: ★
Learn how to DIM variables in VBA: • How to Declare (Dim) a...
VBA Playlist: • Excel VBA & Macros Tut...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
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
Пікірлер: 85
Get access to the full Excel VBA course here 👉 www.xelplus.com/course/excel-vba-excel-macros/
You explain things well, instead of simply showing off your knowledge. Very good.
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.
Thanks for sharing your knowledge on arrays. Nice job Leila.
The best tutorial on array that I have ever seen. You are great.
best youtube chanel for excel, simple, easy and right to the point
@LeilaGharani
4 жыл бұрын
😘
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!
@TheFoxwiz
4 жыл бұрын
That’s exactly what I was thinking
Thanks for that! Great example and very well explained.
Amei sua aula, você explica muito bem. Tem poucas aulas em português sobre esse conteúdo. Obrigo pela ajuda.
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 = Sheet1.Range("A7").CurrentRegion.Value For i = 2 to UBound(Compinfo , 1) ' Compinfo(i, 1), Compinfo(i, 2) Next i
@LeilaGharani
3 жыл бұрын
Great tip!
Thanks. Good example of Array
thanks Leila, extremely helpful and easy to understand. You are a great Instructor and beautiful lady. :)
Excellent. Thank you very much :)
Well explained on something that can be really tricky
@LeilaGharani
6 жыл бұрын
Thank you Wyn - nice to see you here :)
It was great example of 2 dimensional Array, which was quick well explained... 👌
@LeilaGharani
6 жыл бұрын
Thank you. Glad you find the explanation easy to follow :)
Excellent teacher! I also take your courses on Udemy. Well done
Thanks alot you guided me toi much
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
OMG SHE'S GORGEOUS!!!
Great! VBA Queen
Excellent. Thank you !
@LeilaGharani
5 жыл бұрын
You're very welcome Paulo.
Thanks for the array fun : )
@LeilaGharani
6 жыл бұрын
You're very welcome Mike.
Thank you
Sooo useful! Thanks Leila :-)
@LeilaGharani
6 жыл бұрын
You're very welcome Alex :)
Excellent
Excellent lady
Helpful video. I added code at the end of the line for adding a new sheet. "Set ShNew = NewBook.Worksheets.Add(after:=Sheets(Sheets.Count))" 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).
i love your videos
Very very nice video. Very well explained
@LeilaGharani
6 жыл бұрын
Thank you Ashok. Glad you find it easy to follow.
Great video
@LeilaGharani
6 жыл бұрын
Thank you. I'm glad you like it.
superb ms L
well done
Thanks Leila :)
@LeilaGharani
6 жыл бұрын
You're very welcome John.
thanks.
thank you
@LeilaGharani
6 жыл бұрын
You're very welcome Mohamed
Great
Struggling to understand what kind person would give a dislike to this video.
Good one. You can use dictionary in VBA which is more useful than array
It's great explanation. I love u Laila.
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.
@LeilaGharani
6 жыл бұрын
:) Agree - I wouldn't ever think of writing this code if I had to do this once. if I had to repeat it every week on a larger set, then probably yes...
@BillSzysz1
6 жыл бұрын
Absolutely agree, Leila :-))
Nice
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.
Hi Leila, Your videos are great..really easy to understand and follow..Can you also make a video on how to communicate to microsoft sharepoint via VBA..I am looking for some basic tasks like - Download a file / folder from SP to local drive, Upload file to SP, check for existence of file in particular folder of SP, count the number of files in specific folder of SP, etc
@LeilaGharani
6 жыл бұрын
Thanks Manish - Glad you find the videos easy to follow. I added this to my list.
Merci
@LeilaGharani
5 жыл бұрын
My pleasure.
Hi Leila, thank you for all your wonderful videos. I am hoping you could help me. I am using vba to and an array to break up a large list of over 300 names I want my array to have a size of 64 names unless the last name in the array matches the 65th name.
Hi Leila your videos are really nice my question is that we can do this by loop having 2 variables one will store the company name and second manager and then we can run the loop.. I don't have any knowledge about Ubound and Lbound can you tell.me the difference please please
@LeilaGharani
5 жыл бұрын
Yes that will work too. Ubound is the upper limit of the array and Lbound is the lower limit. These are used to get the size of the array.
thank you so much Ms. Leila kindly please let me know how to use barcode for the same product and make the qty bar add one as much as you scan the same product hope you understand what i mean. thanks again
@LeilaGharani
6 жыл бұрын
You're welcome Ismail. Not sure about your query though - best is to post your specific question with a sample file either here: www.mrexcel.com/forum or here: www.excelforum.com/ - you can post a link to your question here too and if I get a chance I can take a look, otherwise you will get answers from others...
CompInfo = Range("A7:B14") works as well to populate the array directly (?)
@LeilaGharani
6 жыл бұрын
Thank you for your comment :) Yes - we cover variant arrays separately in the course. The aim here is to be able to visualize a two dimensional array. I find that's easiest to do when referencing rows and columns....
Spending more time with Leila than with the Mrs.... 8-)
@LeilaGharani
4 жыл бұрын
Haha. I hope she'll understand :)
I don’t suppose we could use strings to refer to data, rather than numbers for the bounds? I have an input which is a strong, and I want to use that as the variable being used to store data in the array. Eg. Input “species=dog”, array(species) = array(species) + 1
Hi Leila! Thak you very much for sharing your knowledge on VBA with your superb posts and channel. In particular, I found this one as a great example of 2 dimensional Array. However, code creates worksheets in an inverse order, that is to say, the first one is the "Pes" worksheet, wich is the last one item of the list. I have been trying: For r = 8 To 1 Step -1, but VBA gives me an error. However the same idea works with an 1 dimesional Array. Please, How could I try to obtain worksheets in its order? Thanks a lot...
@robertbateman3665
4 жыл бұрын
Pablo Alonso: Try using ...Worksheets.Add(After:=Sheets(Sheets.Count)). This will add the new sheet to the end of the sheet tabs. Note: You'll still have "Sheet1" in the new book. You might want to write something to find if Sheet1 exists & if so, delete it. Have a great day!
Hello I am trying to find an vba code to copy and paste every range of rows like(A1:C19).copy and paste in word file as image then copy from (A20:C39) and paste in the same word file and so on for the rest of the table in excel. they have told me that I need to use the looping in excel but I don't know exactly how to do it …. please help if you can
People, don't use for loop, it's not fast. Just equal the range to some array and that's it.
👍👍👍👍👍👍👍
WoW = Wonder Woman = Leila Gharani
How can check if array array is empty or not because iam get error if ido like this and array not empty Dim arr as variant Arr = range ("A1"). Currentregion. Value If arr =empty then 🌹
@yeahnick4260
5 ай бұрын
Dim Arr as variant Arr = Range("A1").CurrentRegion.Value if IsArray(Arr) = True then ( ... ) Else ( ....) end if
I created first button and copy paste it to the Userform but it did not Prompt: "You already have a control Name XXX. Do you want to create a control array?" so my question is how do I create Control array of Command buttons. what are the steps to follow? Am I missing something? I am new to this Topic Please help
Hi mam how can it make Dynamic?
Aswm
Please how can I convert a List of cells to an Array e.g A 1 by 9 to a 3 by 3 array arrangement, such that the Array is linked to the List Data? Something like this- 1 2 3 4 5 6 7 8 9 To 123 456 789
@Permaclok
3 жыл бұрын
i = 1 For j = 1 to 9 Arr(i,j) = cells(1,j) If(j / i > 3) then i=i+1 End if Next j
May I get your email id need a help in VBA application development