Transfer Specific Data from one Worksheet to another for Reports

Home More Excel (VBA) Training Videos
     Webhosting by Godaddy     MS Excel Books





I would like to create a quick report from data in one worksheet by transferring relevant data to another worksheet. For example, I would like to transfer all data about projects in progress to another worksheet every time I open my workbook. Is it possibel to achieve this using VBA?
Josh

So Josh would like to transfer specific data from one worksheet to another to create a quick report so that he can quickly determine all the tasks or projects that have been completed and the tasks that need to be done.

The complete VBA code for the transfer of data from Sheet1 to Sheet2 is given below:
Private Sub Workbook_Open()
Dim i, LastRow

LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet2").Range("A2:I500").ClearContents
For i = 2 To LastRow
If Sheets("Sheet1").Cells(i, "E").Value = "" Then
Sheets("Sheet1").Cells(i, "E").EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

After the data is transferred to Sheet2 we can clean up the first sheet (Sheet1) using the following VBA code:
Sub deleteRowsTransferred()
Dim i, LastRow
 LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
 
 For i = 2 To LastRow
 If Sheets("Sheet1").Cells(i, "E").Value = "" Then
 Sheets("Sheet1").Cells(i, "E").EntireRow.Delete
 End If
 Next i
End Sub

In this manner we have create two reports quickly - one having the tasks in progress and the other having the completed tasks.
Watch the training video below:

Transfer Specific Rows of Data from One Worksheet to Another to Create Quick Reports

Watch the video on YouTube

Reference

     Webhosting by Godaddy     MS Excel Books
Home More Excel (VBA) Training Videos