Copy coloured cells to another spreadsheet

[ELEANOR1690]

[TABLE="width: 571"]
<tbody>[TR]
[TD]ROUTINE MAINTENANCE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bus No[/TD]
[TD]Job No[/TD]
[TD="colspan: 3"]Type[/TD]
[TD]ins[/TD]
[TD]VOR[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]NE[/TD]
[TD]TYRE REPAIR[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]NE[/TD]
[TD]TYRE REPAIR[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]NE[/TD]
[TD]TYRE REPAIR[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]NE[/TD]
[TD]TYRE REPAIR[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]NE[/TD]
[TD]TYRE REPAIR[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]NE[/TD]
[TD]TYRE REPAIR[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If the above range as no background colour I would like it to be copied to the next sheet.

Sheets are in date range IE sheet 1 is named 16-SEP-2018 sheet 2 17-SEP-2018 sheet 3 18-SEP-2018 ETC

The idea is that any cells not highlighted in the range have to be copied to the next days worksheet

Can anyone help

 

[ELEANOR1690]

Please see link to filehttps://1drv.ms/x/s!AuJO1XWK93UQmAlLO1DvHUiILz2S

 

[vcoolio]

Hello Eleanor,

The link doesn't work. Please use a free file sharing site (e.g. Drop Box).

Thank you,
vcoolio.

 

[ELEANOR1690]

try thishttps://www.dropbox.com/s/4kxezo6qwdmsquf/Daily%20Workbook%202018-06.xlsm?dl=0

 

[vcoolio]

Hello Eleanor,

It seems as if we are dealing solely with the range A8:G18.

I noticed a couple of things in your workbook that haven't helped our cause:

1) You have placed the code in the worksheet module. This code is not an event code and needs to be placed in a standard module and assigned to a button.
2) Each sheet has merged cells in the area we are dealing with, viz. Range A8:G18. Merged cells create havoc with VBA codes and need to be avoided.

Following is the revised code which also "unmerges" the merged cells in the range we are dealing with. You will still be able to successfully format the cells without merging any of them.

Sub CopyData()

Dim sh As Worksheet: Set sh = ActiveSheet

Application.ScreenUpdating = False

With sh.Range("A7:G18")
        [COLOR=#ff0000].UnMerge
        sh.Next.Range("A8:G18").UnMerge[/COLOR]
        .AutoFilter 1, , 12
        .Columns("A:G").Offset(1).Copy
        sh.Next.Range("A18").End(3)(2).PasteSpecial xlValues
        .AutoFilter
End With

Application.ScreenUpdating = True
Application.CutCopyMode = False
sh.Next.Select

End Sub

Following is the link to your file with the code implemented. I have created a "NEXT" button on three of the date sheets so that you can see how it works. On clicking on the button, the code will transfer each row of data that is NOT high-lighted (based on Column A) to the next sheet.

http://ge.tt/8c3K3cr2

I hope that this helps.

Cheerio,
vcoolio.

 

[ELEANOR1690]

Thank you so much for this it has been a fantastic help unfortunately the link takes me to the site but says there are no share files

 

[ELEANOR1690]

I can create the button but cannot seem to attach the code hopefully this will be the last time I have to bother you

 

[vcoolio]

Hello Eleanor,

Try it again here:-

http://ge.tt/52QoUcr2

I can create the button but cannot seem to attach the code

Right click on the shape and select "Assign Macro". From the "Assign Macro" pop-up, select CopyData (the name of the macro). Click OK.

Cheerio,
vcoolio.