Pick lines and copy depends to another cell sheet

[pantakos]

Hello all,

In excel I need to pick from a sheet lines contains data from another sheet, depending on the cell value.

To clarify, from the attached file you can see Sheet1 with data, 24%, 13% and 6% numbers at column B .

I need to find out if the values from B (from 24% can be found in column F at sheet1 , and if there are copied (the whole row) to the last sheet (φυλλο5)

The same for 13% and 6% , the results to be copied to another sheet.

I hope you understand and help me or give me some tips.

Thank you !

example

Loading Google Sheets

Gerasimos

posted to Pick lines and copy depends to another cell sheet - OzGrid Free Excel/VBA Help Forum

 

[dermie_72]

you could use a countif statement in a column in sheet1 to see if they are present on the % sheets and then filter the items that are greater than 1. Copy all of those items to the last sheet in the first vacant cell, then repeat the process for each of the other sheets.

 

[rowebca]

You can try something like this:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim M As Variant
'# cleaning destination worksheet
Sheet5.Activate
ActiveSheet.Cells.Select
Selection.Delete Shift:=xlUp
Sheet2.Activate
'# runing the loop to check if values are found in sheet1 / F
For i = ActiveSheet.Range("b7000").End(xlUp).Row To 1 Step -1
M = Application.Match(ActiveSheet.Cells(i, 2).Value, Sheet1.Range("F1:F" & Sheet1.Range("F7000").End(xlUp).Row), 0)
If Left(CStr(M), 3) <> "Err" Then
'#COPY ROWS
ActiveSheet.Rows(i).Select
Selection.Copy
Sheet5.Select
ActiveSheet.Rows(i).Select
Selection.Insert Shift:=xlDown
'#END COPY ROWS
Sheet2.Activate
End If
Next i
Sheet5.Activate
ActiveSheet.Columns("b:b").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.Calculation = xlCalculationAutomatic

I hope I understood correctly.

Regards,
GB