Create Reports with Loops

How to create reports quickly and easily with loops in VBA. A user has downloaded data from a website and now she wishes to create a report but she is having difficulties about transferring data from sheet1 to sheet2. We describe the complete process of transferring data from sheet1 to sheet2 step by step using two types of looping methods in VBA: (i)Do While Loop and (ii) For Next Loop. Watch the video below:

 

Here’s the complete VBA code including the advanced option of copy paste using ‘destination’:

Module 1

Sub createReportUsingDoWhileLoop()
‘Let’s start at row 2. Row 1 has headers
x = 2
Dim classification As String, year As Long

year = Application.InputBox(“Enter the year.”)
classification = Application.InputBox(“Enter the classification code.”)

Worksheets(“Sheet1”).Activate
Application.ScreenUpdating = False
‘Start the loop
Do While Cells(x, 1) <> “”
‘Look for name
If Cells(x, 1) = year And Cells(x, 12) = classification Then
‘copy the row if it contains ‘classification’
‘Worksheets(“Sheet1”).Rows(x).Copy
‘Find the first blank row row in Sheet2
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Worksheets(“Sheet1”).Range(Cells(x, 1), Cells(x, 12)).Copy Destination:=Sheets(“sheet2”).Cells(erow, 1)
‘Go to Sheet1. Activate it. We want the data here
‘Worksheets(“Sheet2”).Activate
‘Find the first empty row in Sheet2
‘erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
‘Paste the data here
‘ActiveSheet.Paste Destination:=Worksheets(“Sheet2”).Rows(erow)
End If
‘go to sheet Sheet1 again and actvate it
‘Worksheets(“Sheet1”).Activate
‘Loop through the other rows with data
x = x + 1
Loop
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub

Module 2

Sub createReportUsingForLoop()
‘Let’s start at row 2. Row 1 has headers
Dim x As Long, lastrow As Long
lastrow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
‘x = 2
Dim classification As String, year As Long

year = Application.InputBox(“Enter the year.”)
classification = Application.InputBox(“Enter the classification code.”)

Worksheets(“Sheet1”).Activate
Application.ScreenUpdating = False
‘Start the loop
For x = 2 To lastrow
‘Look for name
If Cells(x, 1) = year And Cells(x, 12) = classification Then
‘copy the row if it contains ‘classification’
‘Worksheets(“Sheet1”).Rows(x).Copy
Worksheets(“Sheet1”).Range(Cells(x, 1), Cells(x, 12)).Copy
‘Go to Sheet1. Activate it. We want the data here
Worksheets(“Sheet2”).Activate
‘Find the first empty row in Sheet1
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
‘Paste the data here
ActiveSheet.Paste Destination:=Worksheets(“Sheet2”).Rows(erow)
End If
‘go to sheet Sheet1 again and actvate it
Worksheets(“Sheet1”).Activate
‘Loop through the other rows with data
Next x
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub

Download the sample file for practice:

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

2 thoughts on “Create Reports with Loops”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.