Moving comments along with cell data

[Chewyhairball]

Hi

The following code when run moves the current selected cell and the 5 cells to the right to whatever destination cell is selected in the Inputbox.
It works good but I cannot figure out how to also move any comments that might be in a cell. Specifically the 5th offset cell.

I have wasted the best part of a day trying all sorts but nothing works
As usual any help is greatly appreciated

Sub MoveCells()
Dim fromrange As Range
Dim torange As Range

Set torange = Application.InputBox(Title:="Select Destination", prompt:="Select where you want to item/s to be moved to then click OK", Type:=8)
Set fromrange = Range(ActiveCell, ActiveCell.Offset(0, 5))

Range(torange, torange.Offset(0, 5)).Value = fromrange.Value
fromrange.ClearContents

End Sub

 

[rlv01]

Range(torange, torange.Offset(0, 5)).Value = fromrange.Value

Use copy/paste instead of range value assignment statements like the one above.

Sub MoveCells()
    Dim fromrange As Range
    Dim torange As Range
    
    On Error Resume Next
    Set torange = Application.InputBox(Title:="Select Destination", prompt:="Select where you want to item/s to be moved to then click OK", Type:=8)
    On Error GoTo 0
    
    If Not torange Is Nothing Then
        Set fromrange = ActiveCell.Resize(1, 6)
        fromrange.Copy
        torange.PasteSpecial xlPasteValues
        torange.PasteSpecial xlPasteComments
        fromrange.ClearContents
    End If
End Sub

 

[Chewyhairball]

Range(torange, torange.Offset(0, 5)).Value = fromrange.Value

Use copy/paste instead of range value assignment statements like the one above.

Sub MoveCells()
    Dim fromrange As Range
    Dim torange As Range
   
    On Error Resume Next
    Set torange = Application.InputBox(Title:="Select Destination", prompt:="Select where you want to item/s to be moved to then click OK", Type:=8)
    On Error GoTo 0
   
    If Not torange Is Nothing Then
        Set fromrange = ActiveCell.Resize(1, 6)
        fromrange.Copy
        torange.PasteSpecial xlPasteValues
        torange.PasteSpecial xlPasteComments
        fromrange.ClearContents
    End If
End Sub

Hi

Thanks for the reply. I can't use copy and paste. I chose the .value way of doing it as I didn't want to also copy and paste any conditional formatting in the cells.

Rory

 

[Jaafar Tribak]

rlv01's code won't copy any conditional formatting as its uses PasteSpecial

 

[Chewyhairball]

Range(torange, torange.Offset(0, 5)).Value = fromrange.Value

Use copy/paste instead of range value assignment statements like the one above.

Sub MoveCells()
    Dim fromrange As Range
    Dim torange As Range
   
    On Error Resume Next
    Set torange = Application.InputBox(Title:="Select Destination", prompt:="Select where you want to item/s to be moved to then click OK", Type:=8)
    On Error GoTo 0
   
    If Not torange Is Nothing Then
        Set fromrange = ActiveCell.Resize(1, 6)
        fromrange.Copy
        torange.PasteSpecial xlPasteValues
        torange.PasteSpecial xlPasteComments
        fromrange.ClearContents
    End If
End Sub

Thanks riv01. That works great!