Extraction of data from one sheet to another - reg

Dear all,

I need to extract some data from sheet 1 based on selected criteria on sheet 2. I have attached the excel for your reference.

******
sheet 1 has overall data from which, Selected criteria Bolts in sheet 2 should be identified and extracted for the corresponding project. If the selected item is not available in the project, it should return 0.

thanks in advance.

regards
Bubalan

I propose use pivot table

I think Pivot table cannot be used , since there will be blank cells and some rows inserted in the future

See attached file as sample. You can play with pivot table to achieve your target

thanks , will check that

Thanks man. i have tried a vba code, please find below:

Sub Update()

Application.Worksheets("General").Activate



'*****identifying row numbers
For j = 4 To ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row

a = Cells(j, 3)

If Cells(j, 3) <> "" Then
Cells(j, 4) = Sheets("Sectionwise").Range("B:B").Find(a).Row

Cells(j, 5) = (Sheets("Sectionwise").Range("B:B").Find(a).End(xlDown).Row) - 1
Else

End If

Next j
'**********identifying row number of department
Dim I As Integer


For I = 4 To Sheets("Sectionwise").Range("C" & Rows.Count).End(xlUp).Row

b = Cells(2, 3)

x = Cells(I, 4)

If IsEmpty(x) = True Then GoTo department

y = Cells(I, 5)
Rx = "C" & x
Ry = "C" & y

temp_range = Rx & ":" & Ry

Worksheets("Sectionwise").Activate
With Sheets("Sectionwise")
.Range(temp_range).Select
End With
Application.Worksheets("General").Activate



On Error Resume Next
Cells(I, 6) = ClearContents
Cells(I, 6) = Worksheets("Sectionwise").Range(temp_range).Find(b).Row
On Error Resume Next

'Cells(j, 6) = temp_loc

Worksheets(Cells(2, 3)).Activate
department:
Next I
'********* populating manhour for corresponding department

a = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row

For k = 4 To a

If Cells(k, 6) <> "" Then

b = Cells(k, 6)

For m = 7 To 27

Cells(k, m) = Worksheets("Sectionwise").Cells(b, m)

Next m

Else

For m = 7 To 27

Cells(k, m) = ClearContents

Next m

End If

Next k


End Sub