Write to other sheets cells and include Cells.SpecialCells(xlCellTypeComments)
 

With this line of code working just fine, how do make it include cell comments when it writes to the other sheets.

Sheets(UCase(varCheck(n, m)) & shName).Cells(Rows.Count, 1) _
.End(xlUp)(2).Resize(2, 8) = .Cells(n + 1, 1).Resize(2, 8).Value


I guess there needs to be a reference to:

Cells.SpecialCells(xlCellTypeComments)

but I have not been able to blend it in.


Regular Range.Copy Destination works but writing to a range does not.

Thanks,

Howard

Write to other sheets cells and include Cells.SpecialCells(xlCellTypeComments)
 

Hi Howard,

Am Thu, 3 Dec 2015 17:45:02 -0800 (PST) schrieb L. Howard:

With this line of code working just fine, how do make it include cell comments when it writes to the other sheets.

Sheets(UCase(varCheck(n, m)) & shName).Cells(Rows.Count, 1) _
.End(xlUp)(2).Resize(2, 8) = .Cells(n + 1, 1).Resize(2, 8).Value

do you want to create comments or add text to existing comments in this
range? Are the comment cells in the same cell in source and target
sheet?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Write to other sheets cells and include Cells.SpecialCells(xlCellTypeComments)
 

Sheets(UCase(varCheck(n, m)) & shName).Cells(Rows.Count, 1) _
.End(xlUp)(2).Resize(2, 8) = .Cells(n + 1, 1).Resize(2, 8).Value

do you want to create comments or add text to existing comments in this
range? Are the comment cells in the same cell in source and target
sheet?


Regards
Claus B.

Hi Claus,

The comments are on the source sheet and in the range that is to be written to the destination sheet.

The code as it is writes the cell values to the other sheet/s but does not bring the comments, if any, with the values.

The comments are notes about the values in the row. Don't want to do anything with the text in the comment. Just need the comment to be with the data after it is written to the other sheet/s.

Howard

Howard

Write to other sheets cells and include Cells.SpecialCells(xlCellTypeComments)
 

Hi Howard,

Am Thu, 3 Dec 2015 23:13:51 -0800 (PST) schrieb L. Howard:

The comments are on the source sheet and in the range that is to be written to the destination sheet.

The code as it is writes the cell values to the other sheet/s but does not bring the comments, if any, with the values.

The comments are notes about the values in the row. Don't want to do anything with the text in the comment. Just need the comment to be with the data after it is written to the other sheet/s.

then it will be faster if you copy the range. Then the comments will go
to the target sheet and you don't have to loop through the specialcells.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Write to other sheets cells and include Cells.SpecialCells(xlCellTypeComments)
 

then it will be faster if you copy the range. Then the comments will go
to the target sheet and you don't have to loop through the specialcells.


Regards
Claus B.

Hi Claus,

Here is the whole code, it has a lot of work to do with about 800 +/- rows, written to as many as five sheets.

I believe we can live without the comments on the other sheets. One can refer back to the main sheet for the comment info.

Writing the comments over with the row data was going to be a suggestion, and not a current and present need.

So we can stay with the good fast working code as is.

Thanks.
Howard


Sub Copy_ALL_MASTER()
Dim LRow As Long, n As Long, m As Long
Dim varCheck As Variant

Const shName = "-Wellford Addresses"

With Sheets("Wellford Addresses-ALL, MASTER")

LRow = .Cells(Rows.Count, 1).End(xlUp).Row
varCheck = .Range("I2:M" & LRow)

For n = LBound(varCheck) To UBound(varCheck)

For m = LBound(varCheck, 2) To UBound(varCheck, 2)

If Len(varCheck(n, m)) 0 Then
If Sheets(UCase(varCheck(n, m)) & shName).Cells(2, 1) < "" Then

Sheets(UCase(varCheck(n, m)) & shName).Cells(Rows.Count, 1) _
.End(xlUp)(4).Resize(2, 8).Value = .Cells(n + 1, 1).Resize(2, 8).Value

Else
Sheets(UCase(varCheck(n, m)) & shName).Cells(Rows.Count, 1) _
.End(xlUp)(2).Resize(2, 8) = .Cells(n + 1, 1).Resize(2, 8).Value
End If

End If

Next m

Next n

End With

End Sub