Macro to copy all highlighted cells in multiple sheets in a workbook to master sheet

[DannyBoy1985]

Hello,

I am a complete novice to vba so please bear with me. I am trying to get a bit of code that will take cells highlighted in yellow in multiple sheets of a workbook and paste as values only these cells into a mastersheet that has exactly the same format in terms of structure and headers. I would like the highlighted cells in each sheet to be pasted in exactly the same place as the master sheet i.e. if cell a57 is highlighted in sub sheet, it is pasted directly into cell a57 of the master sheet. All cells not highlighted in each subsheet is ignored and is not pasted across to the Master.

I have seen similar posts, but this should be hopefully easier as others have required the subsheet data to be posted in a different place in the master. Can any of you kind people please help me?

I basically have 4 different updates to the same master sheet (with all updates highlighted_ and I am essentially trying to consolidate all changes into a new master schedule.

Thanks

 

[mse330]

Hi DannyBoy1985 & welcome to Mr. Excel !

Try below code ...

Sub test()

Dim ws As Worksheet, MainWs As Worksheet, cell As Range
Set MainWs = Sheets("master") '<-- change name as needed

For Each ws In Sheets
   If ws.Name <> MainWs.Name Then
      For Each cell In ws.UsedRange
         'if your cells are colored through conditional formatting, delete/comment below line & uncomment the line after
         If cell.Interior.Color = vbYellow Then MainWs.Range(cell.Address) = cell.Value
         'If cell.DisplayFormat.Interior.Color = vbYellow Then MainWs.Range(cell.Address) = cell.Value
      Next
   End If
Next
    
End Sub

 

[DannyBoy1985]

You sir, are a genius. Thank you. Is there any way to adapt the code to only pick up cells in vbYellow in specific worksheets and update the Master. Just in case I have other sheets in the workbook that have yellow highlighted cells, but i do not want carried into the master

 

[DannyBoy1985]

and separately, the code you kindly provided pastes values only. how would you adapt it to paste;

1. values and the yellow highlights in the subsheets
2. formulas in the subsheets

 

[mse330]

Try below code .. This should copy cell from subsheets as is (format, formula ... etc.) & you keep specify sheet names of which you need to check

Sub test()

Dim ws As Worksheet, MainWs As Worksheet, cell As Range
Set MainWs = Sheets("master") '<-- change name as needed

For Each ws In Sheets(Array("Sheet1", "Sheet2")) 'add sheet names
   If ws.Name <> MainWs.Name Then
      For Each cell In ws.UsedRange
         'if your cells are colored through conditional formatting, delete/comment below line & uncomment the line after
         If cell.Interior.Color = vbYellow Then cell.Copy MainWs.Range(cell.Address)
         'If cell.DisplayFormat.Interior.Color = vbYellow Then MainWs.Range(cell.Address) = cell.Value
      Next
   End If
Next
    
End Sub

 

[DannyBoy1985]

Thank you so much