COPY A ROW BASED ON FONT COLOR

[geno32080]

Hello Excel community,
I have been searching every where. I'm looking for a a macro that when activated with a command button will search the active sheet column B thru F, rows 2 thru 50. find cells with red uppercase font and copy them. I set up a macro so that when the user types in that cell range and tabs to the next cell it changes that font to uppercase and also changes the font to red. The new macro needs to copy that random row, and then will need to paste it to B6, B7, B8, B9 and b10 on a different sheet in the same work book. Is that even possible? any help will be greatly appreciated. Thank you.
Below is an example, copy B2 (customer name) on the active work sheet and paste to B6 on the other sheet, copy C2 (company name) and paste B7, copy D2( manufacture) and paste to B8, copy E2 (model number)and paste to B9 and lastly copy F2 ( phone number) and paste to B10 on the other sheet. The copy feature needs to search the active sheet columns B thru F and Rows 2 Thru 50.

 

[geno32080]

Maybe someone can point me in the right direction? I have been reading a lot of different approaches. Seems the "IF" factor maybe the way to proceed?

 

[Joe4]

I set up a macro so that when the user types in that cell range and tabs to the next cell it changes that font to uppercase and also changes the font to red.

Wouldn't it then make sense to copy it at the same time it is turning red?
Then you don't need to search for it and you don't need a separate macro. Just build it into the one that you already have, that has already identified the row that needs to be moved.

 

[geno32080]

The sheet fills up with data. The thought process behind changing the font to red is so when the user needs to search for a customer name, they would click on that cell and then it would change the entire rows font to red, then click on the button to select that customer.

 

[Joe4]

The sheet fills up with data. The thought process behind changing the font to red is so when the user needs to search for a customer name, they would click on that cell and then it would change the entire rows font to red, then click on the button to select that customer.

But why have it broken up in two macros like that, instead of just doing it in one?
And if it does have to be broken up in two steps like that, if that is the process, wouldn't it already be on the row it needs to copy over (so you really wouldn't need to search for it)?

 

[geno32080]

The worksheet is a customer list, it could do it as it is filled in if it was a new customer each time. Its pasting the information to an work order. So repeat customers is the reason for the search. The existing customers info may end up on row 45?
Kind of why I was thinking the IF maybe the way to write this, unfortunately I'm not that good with the formulas.
Search sheet for Red Font, if true, copy cells, then paste to ? I wish it was that simple.

 

[PeteWright]

Joe4 is right, why not setting up a single macro which the user can run, like a button: [select customer] which then selects the entire row (B to F) and pastes the customer data to a new sheet.
Would that be a solution? This can be easily done with VBA.

 

[Joe4]

OK, I think I understand what you have and what you are trying to do now.
See if this code does what you want. Note that you may need to change "Sheet1" and "Sheet2" to the real names of your sheet, if they are different.

Sub MyCopyData()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim r1 As Long
    Dim r2 As Long

'   Set data sheet to copy from
    Set ws1 = Sheets("Sheet1")
'   Set sheet to copy to
    Set ws2 = Sheets("Sheet2")

'   Set row number to start from on destination sheet
    r2 = 6
   
    Application.ScreenUpdating = False

'   Loop through rows on sheet 1
    ws1.Activate
    For r1 = 2 To 50
'       Check to see if column B is bold and red
        If (Cells(r1, "B").Font.Color = vbRed) And _
            (Cells(r1, "B").Font.Bold = True) Then
'           Copy to destination sheet
            ws1.Range(Cells(r1, "B"), Cells(r1, "F")).Copy ws2.Range("B" & r2)
'           Increment sheet 2 row counter
            r2 = r2 + 1
        End If
    Next r1
           
    Application.ScreenUpdating = True
   
End Sub

Also note that if you want this macro to run automatically after the first macro (without the user having to click a button), you could do that by calling this macro from the end of your other one, i.e.

Sub FirstMacro()
'   All the original code here
    ...

'   Call the second macro
    Call MyCopyData

End Sub

 

[geno32080]

That's amazing. I will give it a try. I will let you know how good it works.Thanks.

 

[geno32080]

Joe your the best! I have been searching what seems like forever for a solution. My biggest hurdle is "How" and then "Is that possible". That works like a charm. Thanks.