VBA copy highlighted rows to another sheet

M4TVD

New Member
Joined
Mar 23, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
I am trying to copy the rows that are highlighted in one sheet into another sheet, the rows that I want to copy are filled yellow.

I have some code already, that will copy only the highlighted rows, however it copies the rows that are not highlighted (but copies them without any values in). I want to only copy the rows with data and ignore the blank rows.

this is the code that I have:

VBA Code:
Sub CopyToBuildOutput()
 
Dim ws As Worksheet, MainWs As Worksheet, cell As Range
Set MainWs = Sheets("Build Output")



For Each ws In Sheets(Array("sheet1")) 
   If ws.Name <> MainWs.Name Then
      For Each cell In ws.UsedRange
          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
 

M4TVD

New Member
Joined
Mar 23, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Thankyou Rick. yes, strange, my origional code copies all of the yellow rows, but it also copies the non yellow rows but leaves the non yellow rows cells empty.

Your code only copies the first yellow row and no other.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Watch MrExcel Video

Forum statistics

Threads
1,132,702
Messages
5,654,820
Members
418,155
Latest member
demasisi

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