Comments

[Prixton]

Hi,
in a workbook we have a booking-list for different rooms. In columns we have
weeks and in rows we have classroom and day monday - sunday. In the cells we
have comments saying subject and teacher. As the system is going out of hand
we´d like to copy the information from a week (one column) and paste it into
a new sheet, say column A, and we also want the comment to be printed in,
say column B.
I have tried to record a macro but it does not work.

Does someone have a solution?

Regards

 

[Debra Dalgleish]

The following code will copy the values and comments from a specific
column to a new sheet:

'===========================
Sub CopyCommentsFromColumn()
'based on a code posted by
'Dave Peterson 2003-05-16
Application.ScreenUpdating = False

Dim commrange As Range
Dim mycell As Range
Dim curWks As Worksheet
Dim newwks As Worksheet
Dim i As Long
Dim intCol As Integer
Dim lngRow As Long
intCol = Application.InputBox("Which column number?")

'count the rows in the selected column
Set curWks = ActiveSheet
lngRow = curWks.Cells(Rows.Count, intCol).End(xlUp).Row

Set commrange = curWks.Range(curWks.Cells(1, intCol), _
curWks.Cells(lngRow, intCol))

Set newwks = Worksheets.Add

newwks.Range("A1:B1").Value = _
Array("Class", "Teacher/Subject")
newwks.Name = "Week " & Format(intCol, "00")

For Each mycell In commrange
With newwks
i = mycell.Row + 1
On Error Resume Next
.Cells(i, 1).Value = mycell.Value
.Cells(i, 2).Value = mycell.Comment.Text
End With
Next mycell

Application.ScreenUpdating = True

End Sub
'===============================

 

[Prixton]

Debra,
I cannot thank you enough!
Best regards

Prixton

 

[Debra Dalgleish]

You're welcome! There are variations of the code here:

http://www.contextures.com/xlcomments03.html