PDA

View Full Version : [SOLVED] if find specify word copy all the row to anther sheet



parscon
06-06-2019, 02:06 AM
Hi everyone .
Hope you are fine
I need you help regarding very Big Excel file .
I need a VBA That check from column P to Z and if find specify word copy all the row to another sheet and i need open search dialog for write the specify word .
Main Data is in Sheet1 and Data must placed is Sheet2
Really appreciate for your time and effort.

dotchiejack
06-06-2019, 05:38 AM
Like this?
Code tranfers data from active sheet to the sheet with name Sheet2

Sub dotch()
myValue = InputBox("Enter the word you are looking for.", "Word?")
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1 'assuming that row 1 contains headers.
For j = 16 To 26
If Cells(i, j) = myValue Then
Range("A" & i & ":Z" & i).Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1) ' Change sheet name and range if necessary.
Cells(i, 1).EntireRow.Delete 'Watch out! This line of code deletes the rows on sheet 1 that have been transferred to sheet2
End If
Next
Next
End Sub

parscon
06-06-2019, 05:49 AM
Thanks for your help but ıt does not works i uploaded the sample file for example when i search L180C HL it does not gıve me any result but it is available Can you please try it again .

Rob342
06-07-2019, 02:18 AM
parscon

Your database is rubbish no headers, fields missing and to many variants in the search field
I have managed to cobble together something that works it will find anything for ex L180C HL or partial text
This will find everything to what you input , probably not the result you were after but try it

NB it does not delete anything from sheet1 just puts the search data into sheet2 ok
Rob

parscon
06-07-2019, 02:28 AM
Rob342ö Really appreciate for your great works and help.