October 29, 2018

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:

2 thoughts on “Create Reports with Loops

  1. Very useful.
    I really need your help and please contact once you’ve read this.
    Thank you sir.

Comments are closed.