BigBeachBananas
Active Member
- Joined
- Jul 13, 2021
- Messages
- 450
- Office Version
- 365
- Platform
- Windows
- MacOS
I'm trying to copy and paste cells from sourceSheet to destSheet. The macro works fine if it's not a merged cell, however, when it comes to merged cells it doesn't paste in the values. Can someone help me with the merged cell block below?
It's fine to assume if the sourceCell is a merged cell, then the destCell will also be a merged cell of same size and address.
T.I.A
It's fine to assume if the sourceCell is a merged cell, then the destCell will also be a merged cell of same size and address.
T.I.A
VBA Code:
For Each sourceCell In sourceSheet.UsedRange
' Check if the cell's background color matches the specified RGB color
If sourceCell.Interior.Color = RGB(189, 218, 129) Then
' Determine the destination cell
If sourceCell.MergeCells Then
'ISSUE STARTS FROM HERE-----------------------------------------------------------------------------------------------
' If merged, find the first cell in the merged range
Set destCell = destSheet.Cells(sourceCell.mergeArea.Cells(1, 1).Row, sourceCell.mergeArea.Cells(1, 1).Column)
' Merge the destination cell to match the source cell's merged state
destCell.Resize(sourceCell.mergeArea.Rows.Count, sourceCell.mergeArea.Columns.Count).Merge
' Copy the source cell's formatting
'sourceCell.Copy
destCell.value = sourceCell.value
'ISSUE ENDS HERE--------------------------------------------------------------------------------------------------------
Else
' If not merged, find the corresponding cell in the destination sheet
Set destCell = destSheet.Cells(sourceCell.Row, sourceCell.Column)
' Copy and paste as values only
destCell.value = sourceCell.value
End If
Debug.Print destCell.value
Debug.Print destCell.Address
End If
Next sourceCell