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

DannyBoy1985

New Member
Joined
Dec 19, 2020
Messages
4
Office Version
  1. 365
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
716
Office Version
  1. 365
Platform
  1. Windows
Hi DannyBoy1985 & welcome to Mr. Excel !

Try below code ...
VBA 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
 
Solution

DannyBoy1985

New Member
Joined
Dec 19, 2020
Messages
4
Office Version
  1. 365
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

New Member
Joined
Dec 19, 2020
Messages
4
Office Version
  1. 365
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

Well-known Member
Joined
Oct 18, 2007
Messages
716
Office Version
  1. 365
Platform
  1. Windows
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

VBA 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(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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,846
Messages
5,627,235
Members
416,232
Latest member
Ash1432

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top