Need to move conditional formatted highlighted rows in one sheet to another sheet

[David117]

Hope someone can help, I need a formula / macro to to copy conditional formatted highlighted rows (RGB 180,198,231) in one sheet to another sheet.
The destination sheet just needs to paste the row information and not the colour also when the information is pasted to the destination sheet the rows are pasted one after the other with no gaps starting from "A2"

Also, note to self, don't say you can help the boss out with their issues

 

[vcoolio]

Hello David,

Try the following code placed in a standard module and assigned to a button:

Option Explicit
Sub Test()

    Application.ScreenUpdating = False
   
    With Sheet1.[A1].CurrentRegion
            .AutoFilter 1, RGB(180, 198, 231), 8
            .Offset(1).EntireRow.Copy
            Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
            .AutoFilter
    End With
   
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

I don't know how your data set is set out but I'm assuming that you have headings in row1 with data starting in row2 and the formatting is in Column A as well.
As I don't know your sheet names, I've used sheet codes ( Sheet1, Sheet2) in the VBA code above which could be the sheet codes for your source/destination sheets anyway.

I hope that this helps.

Cheerio,
vcoolio.

 

[David117]

Hi vcoolio
Thanks for your quick reply.
I've copied this into my spreadsheet and changed the page numbers to Sheet2 & Sheet3 (Sheet 2 being Source of the Conditional Formatting and Sheet 3 being the destination) but nothing seems to happen.
This is way above my understanding so was hoping you might have some idea as to what is going on

 

[vcoolio]

I've copied this into my spreadsheet and changed the page numbers to Sheet2 & Sheet3 (Sheet 2 being Source of the Conditional Formatting and Sheet 3 being the destination) but nothing seems to happen.

Hello David,

Where did you actually place the code?

If you open the VB Editor (Alt + F11), over to the left in the VB Editor, you'll see all your worksheets listed with their actual names in parentheses and the sheet codes, as I have used in the VBA code in my first post, immediately to the left of the sheet names: Sheet1, Sheet2 etc......
Change the sheet codes in the VBA code to suit.

While you're in the Editor, above the sheet list, select Insert then Module. You'll then notice the code field to the right turn white and "Module1" will appear below the sheet list. Paste the code into the white code field then go back to your main sheet, select "Insert" from the ribbon> then "Shapes">select a shape (usually a rectangle type)>click on a spot on your worksheet to place and draw the shape>right click on the shape>select "Assign Macro". An "Assign Macro" dialogue box will appear which will show the subroutine (VBA code) name. Select the name and click OK. The code is now assigned to the shape (the shape is now your button).

Another way to create a button: In the ribbon, select the Developer tab then, in the 'Controls' group, select 'Insert' and click on the drop down arrow. In the Form Controls group, select Button(Form Control) then select a spot on your worksheet where you might like the button to be and create the shape ( the cursor will have turned into a cross-hair which will allow you to draw) to the size you'd like. Assign the VBA code in the same way, as mentioned above for the shape.

Create a copy of your workbook first and do any testing in the copy to ensure you are happy with the results before applying the changes to your actual workbook.

Let us know how it all goes.

Cheerio,
vcoolio.

 

[David117]

Hi vcoolio
Found the issue as to why the macro wasnt working for me, seems there was existing information in the destination sheet (100 rows down) so the macro put the information after this pre-existing information out of eyesight

Thank you so much for your help and sorry for me being such an idiot

Any idea of how to adapt your macro so it clears the destination sheet of information from A2 onwards before it activates the moving of cells

 

[vcoolio]

Hello David,

I'm glad to know that you discovered the source of your problem and I'm happy to have been able to assist.
To clear the destination sheet of data (excluding the headings), add this line of code:-

Sheet2.UsedRange.Offset(1).Clear

just above this line of code:-

With Sheet1.[A1].CurrentRegion

I'm still using the sheet codes here as per post #2 so you may have to change these to suit your actual workbook.

I hope that this helps.

Cheerio,
vcoolio.

 

[David117]

Going to have to be dumb again where within the previous code where do I put the clear contents formula

Option Explicit
Sub Test()

Application.ScreenUpdating = False

With Sheet1.[A1].CurrentRegion
.AutoFilter 1, RGB(180, 198, 231), 8
.Offset(1).EntireRow.Copy
Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
.AutoFilter
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

 

[vcoolio]

Hello David,

The complete code should be as follows:-

Option Explicit
Sub Test()

    Application.ScreenUpdating = False
    
    Sheet2.UsedRange.Offset(1).Clear
    
    With Sheet1.[A1].CurrentRegion
            .AutoFilter 1, RGB(180, 198, 231), 8
            .Offset(1).EntireRow.Copy
            Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
            .AutoFilter
    End With
   
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

Cheerio,
vcoolio.

 

[David117]

Hi vcoolio
Thank you so much for you help
This is amazing

 

[vcoolio]

You're welcome David. I'm happy to have been able to assist.

Cheerio,
vcoolio.