PDA

View Full Version : Need to consolidate comments across spreadsheets



yding93
04-13-2017, 02:25 PM
I have a workbook that includes 1 summary sheet and a number of data sheets.
Each of those sheets have same page layout so for instance cell A1 in summary tab equals the sum of all cell A1s from data sheets.

The cells in datasheets includes comments that describe the data in the cell so I'm wondering if there's anyway I can consolidate all those comments to the summary sheet?

Thank you!

Aussiebear
04-13-2017, 05:39 PM
This may give you a starting point. https://www.extendoffice.com/documents/excel/679-list-all-comments-workbook.html.

snb
04-14-2017, 01:30 AM
Sub M_snb()
for each it in sheets
if it.name<>"summary" then
for each it1 in it.cells.specialcells(-4144)
sheets("summary").cells(it.row,it.column).comment.text sheets("summary").cells(it.row,it.column).comment.text & vblf & it.comment.text
next
end if
next
End Sub

yding93
04-17-2017, 10:19 AM
Thank you for your help!

yding93
04-17-2017, 10:20 AM
Thank you for your code.

I tried your code and the error message "Run-time error '1004': No cells were found" popped.

I changed For Each it1 In it.cells.specialcells(-4144) to For Each it1 In it.Cells.SpecialCells(xlCellTypeComments) but it still won't work.

Do you know why this would happen or how to fix it?