Search for a value in a range, copy only values of next three cells in row to last li

[nitinbali]

I tried a lot by mixing various codes,, unable to get the work done... Please do help me guys... Thanks a ton..

I need to search for the value Diesel and copy next three cells to last line of another sheet. I need to copy only the values, as these are formula based cells.

My Excel sheet goes something like this.. (the sheet extends horizontally with similar fields)


Fuel Machine Meter reading Quantity Fuel Machine Meter reading Quantity
Diesel CAT 03 22 5 Diesel CAT 03 54 54
Diesel CAT 03 22 45 Diesel CAT 03 54 48
- - - - - - - -
- - - - - - - -

[kareninstructor]

Welcome to the forums.

In regards to code you have tried, it would be best to show the code, perhaps it only needs slight changes once someone here sees the code or a suggestion can be made for another method.

Also when you say the last line of another sheet, what is the indicator for the last line? A specific row, one row below the last line used? Is the sheet in the same physical file?

When posting your code please enclose the code into a CODE tag which is available while posting in the formatting area above the text area labeled Code.

[nitinbali]

I am new to this VBA & Macro functions..
I tried searching here n there... Got this code :

Sub customcopy()
Dim strsearch As String, lastline As Integer, tocopy As Integer

strsearch = "Diesel"
lastline = Range("A65536").End(xlUp).Row
j = 1

For i = 1 To lastline
    For Each c In Range("A" & i & ":Z" & i)
        If InStr(c.Text, strsearch) Then
            tocopy = 1
        End If
    Next c
    If tocopy = 1 Then
    Sheet2.Select
        k = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
   
    
    
        Rows(i).Copy Destination:=Sheets(2).Rows(k)
        j = j + 1
        
    End If
tocopy = 0
Next i

End Sub

But this chooses the entire row... I just need to Copy four cells in the row.
And this function only searches in one column... I need it to search Every 5th column... i,e, A,E,I,M,Q n so on....

[Hack]

Excel VBA question moved to Office Development