Macro to move all coloured cells to another sheet/workbook

[Sidicarus]

Hello

I work on a reception desk and need to revamp the processes we use I'm hoping that the use of Macros may make this easier,

So far I have used a Macro I found on a previous thread to move data from one sheet to another I was now hoping to use a macro to move all data if red to either another sheet or workbook.

The sheet in question will move information from 6 columns into another if the colour is red this way making the other sheet neater as it only contains information that is needed at the moment

The information being moved still needs to be stored for other reasons

is this possible?

Many Thanks
Sidicarus

 

[mse330]

Welcome to Mr. Excel

Give the below code a try & let me know if it works for you. You may need to do some modifications but since the info you shared with us is very limited, that's the best that I could do for now

Sub Red_New_Sheet()

Dim Rg As Range
Set Rg = ActiveSheet.UsedRange

Sheets.Add.Activate

Dim lRow As Long
lRow = 1

For Each Cell In Rg
    If Cell.Interior.Color = vbRed Then  ' change the word 'Interior' with 'Font' if the text color is red & not the cell
        ActiveSheet.Range("A" & lRow).Value = Cell.Value
        lRow = lRow + 1
    End If
Next

End Sub

 

[Sidicarus]

Hello Mse330

Thank you for the welcome, I've been using the site for a while but never needed to post anything but I couldn't find a thread or forum that had anything to do with moving coloured fonts/cells

I just tried the code you offered and it did move over the information however it lost all formatting, what details/information would you need in order to get it to move over and stay in the same format as before?

Also is their a way to move it to a certain sheet and it move up the other details so that the old is being filtered out and the new staying ?

I know its going to be difficult due to me not being able to disclose any information regarding to the spread sheet

any assistance would be greatly appreciated

 

[mse330]

Hey Sidicarus,

I guess it was the case for many members where we keep reading/searching old threads until some point of time where we need to ask a question

I have revised the code to incorporate your requests

1. Copy the cell & paste it (preserving all formats/formulas) unlike my previous code where it only took the values
2. You can just change the sheet name code (always prefer to use the code name so the code doesn't crash when someone changes the sheet name)
3. It will delete the old data in the other sheet & replace it with the new data

Let me know if you need any further help

Sub Red_New_Sheet()

Dim Rg As Range
Set Rg = ActiveSheet.UsedRange

Dim ws As Worksheet
Set ws = Sheet2 ' change this to the Sheet Code Name that you wish to use

With ws
    .Columns(1).Insert
    .Columns(2).Delete
End With
  
Dim lRow As Long
lRow = 1

For Each cell In Rg
    If cell.Interior.Color = vbRed Then  ' change the word 'Interior' with 'Font' if the text color is red & not the cell
        cell.Copy
        ws.Range("A" & lRow).PasteSpecial xlPasteAll
        lRow = lRow + 1
    End If
Next

Application.CutCopyMode = False

End Sub