CODEWITHSUNDEEP
excelvba
Luca
Luca

Reputation: 69

Remove cell formatting when copying cells from one range to another

My code for copying a row from one worksheet into another. 

For Each i In Worksheets("BOM").Range(rangeStr)
    Set cell = dataCol.Find(What:=i.Value, LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)

    If cell Is Nothing Then
        MsgBox "part not found"
    Else
        dataRow = "B" & cell.Row & ":I" & cell.Row
        bomRow = "B" & i.Row & ":I" & i.Row
        Worksheets("datasheet").Range(dataRow).Copy Destination:=Worksheets("BOM").Range(bomRow)
    End If

Next

The cells are formatted differently, some have red backgrounds, green etc. How do I copy only the values within the cell and none of the formatting?

Upvotes: 0

Views: 38

Answers (1)

CLR
CLR

Reputation: 12279

You can alter your single .Copy/Destination line to this, to achieve a Copy/Paste Special - Values..

Worksheets("datasheet").Range(dataRow).Copy
Worksheets("BOM").Range(bomRow).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Note: you need the Application.CutCopyMode = False to deselect the Copy range.

HOWEVER, you don't need to Copy at all. Just set the values of the cells to those of the other like so:

Worksheets("BOM").Range(bomRow).Value = Worksheets("datasheet").Range(dataRow)

Upvotes: 2

Related Questions