Macro to copy specific data from multi sheets based on criteria

Closed
Posts
1
Registration date
Tuesday May 27, 2014
Status
Member
Last seen
May 27, 2014
-
Hi,
I am trying to copy specific cells from different sheets to master sheet under the following criteria:

* Master sheet should be generated whenever I run a macro erasing the previous sheet data
* All the Column headings are identical.
* Specific columns should copied into master sheet based on below criteria

If Date (Column C) - Current Date < 30
Then Macro will copy Name, SheetName, ID & Date in master Sheet as mentioned below "Master"..i.e Start from row 11

Example:

Sheet1:
A B C D
Name ID Date RefNo
AA 22 22/06/2014 R001
BB 33 27/06/2014 R004

Sheet2:
A B C D
Name ID Date RefNo
GG 88 22/05/2014 T005
KK 77 18/06/2014 T007

Sheet3, Sheet4.....

Master (output):
B C D E
Name SheetName ID Date

I have googled and managed to find below code bt it didn't worked:
===============================================
Private Sub Test1()
Dim oSheet As Worksheet
Dim oSheetTarget As Worksheet
Dim oCell As Range
Dim iRow As Long

Set oSheetTarget = ActiveWorkbook.Sheets("Master")
iRowTarget = 11

For Each oSheet In ActiveWorkbook.Sheets
Select Case oSheet.Name
Case "Sheet1", "Sheet2"
For iRow = 2 To 5
Set oCell = oSheet.Range("C" & iRow)
If DateDiff("d", Today, oCell.Value) < 30 Then
oSheetTarget.Cells("B" & iRowTarget).Value = oSheet.Range("A" & iRow)
oSheetTarget.Cells("C" & iRowTarget).Value = oSheet.Range("SheetName" & iRow)
oSheetTarget.Cells("D" & iRowTarget).Value = oSheet.Range("B" & iRow)

iRowTarget = iRowTarget + 1
End If
Next
End Select
Next
End Sub
=================================================

Please help in on this. Thanks in advance..