Macro to include more cells for selection to print
 

Hello,
I am by no means a macro expert, just know enough to be dangerous.
I have a macro for a calendar, that looks at all comments in the sheet, then
prints them on a new sheet with the date that they were inserted into.
My delemma is that The Calendar sheet I found ( and then altered) had one
month per sheet.
I put the whole calendar on one sheet, but it will only read notes from
January.

My cells go from B5:AF32 for the calendar dates, whereas the previous one
was only for B5:H10.
Nowhere in the Sub do I see a reference to rows & columns so I can expand
the cells to print from, should there be a comment in them.
What would I have to change in this Sub to make it run through all the cells
and pick out the dates with comments in them, and then list them.

Thank You
Driftwood




Sub FormatForPrinting()
Dim UserSheetCount As Long
Dim cell As range
Dim r As Long, c As Integer
Dim CalendarSheet As Worksheet
Set CalendarSheet = ActiveSheet
UserSheetCount = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Workbooks.Add
Application.SheetsInNewWorkbook = UserSheetCount
Cells(1, 1) = "'" & Format(CalendarSheet.range("MonthName"), "mmmm yyyy")
Cells(1, 1).Font.Bold = True
r = 3
c = 1
For Each cell In CalendarSheet.range("ValidDays")
If CellHasComment(cell) Then
If cell.Comment.Text < "" Then
Cells(r, c) = Format(CalendarSheet.range("MonthName"),
"mmmm") & " " & cell.Text
Cells(r, c + 1) = cell.Comment.Text
r = r + 1
End If
End If
Next cell
Columns("B:B").ColumnWidth = 30
Cells.EntireRow.AutoFit
range("A1").Select
End Sub

Macro to include more cells for selection to print
 

The macro is using the named range CalendarSheet.range("ValidDays"). You can
manually change this range from the worksheet menu Insert - Name - Define and
change the range.


"Driftwood" wrote:

Hello,
I am by no means a macro expert, just know enough to be dangerous.
I have a macro for a calendar, that looks at all comments in the sheet, then
prints them on a new sheet with the date that they were inserted into.
My delemma is that The Calendar sheet I found ( and then altered) had one
month per sheet.
I put the whole calendar on one sheet, but it will only read notes from
January.

My cells go from B5:AF32 for the calendar dates, whereas the previous one
was only for B5:H10.
Nowhere in the Sub do I see a reference to rows & columns so I can expand
the cells to print from, should there be a comment in them.
What would I have to change in this Sub to make it run through all the cells
and pick out the dates with comments in them, and then list them.

Thank You
Driftwood




Sub FormatForPrinting()
Dim UserSheetCount As Long
Dim cell As range
Dim r As Long, c As Integer
Dim CalendarSheet As Worksheet
Set CalendarSheet = ActiveSheet
UserSheetCount = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Workbooks.Add
Application.SheetsInNewWorkbook = UserSheetCount
Cells(1, 1) = "'" & Format(CalendarSheet.range("MonthName"), "mmmm yyyy")
Cells(1, 1).Font.Bold = True
r = 3
c = 1
For Each cell In CalendarSheet.range("ValidDays")
If CellHasComment(cell) Then
If cell.Comment.Text < "" Then
Cells(r, c) = Format(CalendarSheet.range("MonthName"),
"mmmm") & " " & cell.Text
Cells(r, c + 1) = cell.Comment.Text
r = r + 1
End If
End If
Next cell
Columns("B:B").ColumnWidth = 30
Cells.EntireRow.AutoFit
range("A1").Select
End Sub

Macro to include more cells for selection to print
 

Joel,
Thank you for that... 'ya know, I've actually used a named range in one of
my own macros before, just did'nt click until I read your reply.
Cheers
Driftwood

"Joel" wrote:

The macro is using the named range CalendarSheet.range("ValidDays"). You can
manually change this range from the worksheet menu Insert - Name - Define and
change the range.


"Driftwood" wrote:

Hello,
I am by no means a macro expert, just know enough to be dangerous.
I have a macro for a calendar, that looks at all comments in the sheet, then
prints them on a new sheet with the date that they were inserted into.
My delemma is that The Calendar sheet I found ( and then altered) had one
month per sheet.
I put the whole calendar on one sheet, but it will only read notes from
January.

My cells go from B5:AF32 for the calendar dates, whereas the previous one
was only for B5:H10.
Nowhere in the Sub do I see a reference to rows & columns so I can expand
the cells to print from, should there be a comment in them.
What would I have to change in this Sub to make it run through all the cells
and pick out the dates with comments in them, and then list them.

Thank You
Driftwood




Sub FormatForPrinting()
Dim UserSheetCount As Long
Dim cell As range
Dim r As Long, c As Integer
Dim CalendarSheet As Worksheet
Set CalendarSheet = ActiveSheet
UserSheetCount = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Workbooks.Add
Application.SheetsInNewWorkbook = UserSheetCount
Cells(1, 1) = "'" & Format(CalendarSheet.range("MonthName"), "mmmm yyyy")
Cells(1, 1).Font.Bold = True
r = 3
c = 1
For Each cell In CalendarSheet.range("ValidDays")
If CellHasComment(cell) Then
If cell.Comment.Text < "" Then
Cells(r, c) = Format(CalendarSheet.range("MonthName"),
"mmmm") & " " & cell.Text
Cells(r, c + 1) = cell.Comment.Text
r = r + 1
End If
End If
Next cell
Columns("B:B").ColumnWidth = 30
Cells.EntireRow.AutoFit
range("A1").Select
End Sub