Excel VBA COLLECTION LOOPS with Workbook/Sheets Handling Data - Series4

person icon Ajay Parmar

Excel VBA COLLECTION LOOPS with Workbook/Sheets Handling Data - Series4

Data Copy and Paste is now so easy with Collection Loops and Classes use

updated on icon Updated on Sep, 2023

language icon Language - English

person icon Ajay Parmar

architecture icon Office Productivity,Excel


30-days Money-Back Guarantee

Training 5 or more people ?

Get your team access to 19,000+ top Tutorialspoint courses anytime, anywhere.

Course Description

  • In this course, you shall learn about Collection Loops and workbook/worksheet commands
  • Collection Loops is a great topic as it will help you going to work with Ranges, sheets and workbooks with how to define objects and understand the classes.
  • How to copy data from every sheet within a workbook and paste in some other source.
  • How to work with more than 1 workbook using workbook collection loop
  • How to work with cells in a range using for each collection loop.
  • What is the use of a Set key word.
  • How to define objects like sheets and workbooks and ranges
  • How to run one collection loop inside other collection loop and why we need to do this, Its practical course.
  • How to mix other loops like for next and if functions inside collection loops
  • How to open a workbook , how to close a workbook. How to delete a workbook
  • How to delete and select a sheet ,rename a sheet, move a sheet and add a sheet etc.
  • What is the difference between Thisworkbook and Activeworkbook - Interview preparation
  • How to select sheets and workbooks by index and names. Which one we should choose.
  • What are the classes in VBA and what are their methods and properties .
  • How to use dot operator to access the properties and methods of different classes.
  • How to see object browser window in VBA Editor window to see how classes and its members are mentioned.


What will you learn in this course:

  • Do you not want to compile data from different worksheets and workbooks. How to go to different workbooks and pull the data from there and compile it one place. 
  • How to work with so many sheets without bothering about things - what name they have , if they will get deleted in future or if more sheets get added. These collection loops make you a great programmer. in-fact, if you do not know this you can never be a great coder. Come and fall in love with them. Immensely helpful , interesting and powerful. So much fun .
  • Learn how to work with workbooks and worksheets

  • How to open the workbooks, how to close one workbook, specific workbook or all opened workbooks

  • Learn how to delete or add workbooks , how to jump from one workbook to another. From activate files to editing them -we are covering all.

  • How to select or delete or protect or rename or add the worksheets in excel.

  • How to define the workbooks and worksheets in a professional manner using "Set" Keyword. What is the advantage of declaring objects as classes.

  • What happens if we declare set statements anywhere in the code. Does it allowed in vba? Detailed discussion on the proper mentioning of dim and set statements while dealing with objects and classes.

  • How VBA works when it comes to writing syntaxes. Discussion on the object hierarchy and its methods and properties.

  • Questioning rounds in a live session to students to practice more on set keyword.

  • What are collection loops and their basic syntax's - their practical use

  • Loop through worksheets in a workbook

  • Loop through workbooks one by one when they are opened

  • Hoop through cells in range and see the use of those collection loops in projects

  • How to work with multiple sheets like hiding or unhiding them

  • How to copy and paste the data from each of the sheets in a workbook

  • What if you want to copy the data from multiple workbooks , going to each of the worksheet and copying the data and pasting it in one single workbook. This is going to be done using collection loop.

  • What is a dot operator and how objects are classified in VBA. Learn about object methods and properties - What are these and what is the difference between them . Their significance in VBA.

  • Amazing projects for students

  • Online assistance always there for students by me to clear their doubts and questions.


What are the prerequisites for this course?

  • You should know  for next and do while loops.
  • You should know how to declare variables and how to record and edit macros in VBA.
  • You should know IF statements as well to fully benefit from this course.
Excel VBA COLLECTION LOOPS with Workbook/Sheets Handling Data - Series4


Check out the detailed breakdown of what’s inside the course

Collection Loops in VBA
12 Lectures
  • play icon A quick introduction - What we are covering?
  • play icon A quick walk-through about Course 06:25 06:25
  • play icon SHEETS Collection Loop with Object Hierarchy System
  • play icon Worksheet collection Loops 01:10:35 01:10:35
  • play icon Collection Loops and workbook commands - Very important
  • play icon Workbook CollectionLoops 01:20:15 01:20:15
  • play icon Range Collection Loop
  • play icon RangeCollection 44:00 44:00
  • play icon Let us do this Project - Data picking with Filters
  • play icon Project with filters 35:06 35:06
  • play icon One more project - Highly Advance - Data Accumulation
  • play icon Data compile project 27:05 27:05

Instructor Details

ajay parmar

ajay parmar

Data Analytics Trainer

I have been teaching online from 10 years now and teaching is my hobby and passion. I cannot think of anything better than teaching and shaping up students career. Enroll today and take your knowledge to the next level. My training content is my proud and you will not be disappointed. I teach advance Excel, Excel VBA, MS Access, Access VBA, PowerQuery, M code in Powerquery, PowerPivot,DAX Formulas,Power BI and WebScraping using html libraries in VBA.

Show less

Course Certificate

User your certification to make a career change or to advance in your current career. Salaries are among the highest in the world.

sample Tutorialspoint certificate

Our students work
with the Best


Related Video Courses

View More

Annual Membership

Become a valued member of Tutorials Point and enjoy unlimited access to our vast library of top-rated Video Courses

Subscribe now
People having fun around a laptop

Online Certifications

Master prominent technologies at full length and become a valued certified professional.

Explore Now
People having fun around a laptop

Talk to us