Conditional cross-sheet copying

[bjorncl]

Dearest pros ,

I'm having trouble with finding out how to do this in the manner i want it to work. I found some solutions of premade code that almost did the job but i can't seem to get the right code.

WHAT I'M TRYING TO DO:

-> We got an excel file with 2 different tabs. (clients and offers)
-> In the clients sheet i have differten colums (col B - col J)
-> IF colum H = "Offer ready" THEN copy (same row) col B,C,D,E,F,H,I (so every column except G and J) to worksheet "offers"
-> It should be placed at the bottom of the page, seeing as this will be a table containing all offers

As i stated before, i found a solution to copy the entire row, but i'm too inexperienced in excel VBA to actually analyze the code.

Can someone please help me with this ? I'm trying to streamline our workflow in the office and this would be a great help!

Thank you in advance all I'm sure some of you will have the answer ready for typing.

Greetings from Belgium,
Bjorn

 

[mumps]

Hi Bjorn. Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("clients").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Sheets("clients").Range("H2:H" & LastRow)
        If rng = "Offers ready" Then
            Sheets("clients").Range("B" & rng.Row & ":F" & rng.Row).Copy Sheets("offers").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            Sheets("clients").Range("H" & rng.Row & ":I" & rng.Row).Copy Sheets("offers").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Since you didn't mention where on sheet "offers" you wanted the data copied, the macro starts to copy to column A.