Need help writing code for a month change event. At the beginning of
each month, the user will enter the new month's number (i.e. Feb = 2,
Mar = 3, etc) and the macro will go to the previous month's tab (i.e.
user selects 2 for "Feb", macro would go to "Jan" tab), search in
Column "A" for any and all cells that have the word "cancellation" and
copy the data in columns B-K to the "Summary" tab in the next blank
row, starting in column B. Would also be very nice to have it
automatically fill in the month name in Column A of the Summary Tab
(i.e. if user selects month 2, the month to insert in column A would
be "January"). If anyone can help I'd appreaciate it.
Thanks!

Sub test1()

'get month name
OldMonthName = MonthName(ActiveCell.Value - 1)
RowCount = ActiveCell.Row + 1
With Sheets(Left(OldMonthName, 3))
Lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
For MonthRowCount = 1 To Lastrow
If .Cells(MonthRowCount, "A").Value = "Cancellation" Then
ActiveSheet.Range("A" & RowCount).Value = OldMonthName
.Range("B" & MonthRowCount & ":K" & MonthRowCount).Copy _
Destination:=ActiveSheet.Range("B" & RowCount)
RowCount = RowCount + 1
End If

Next MonthRowCount
End With

End Sub


"bjohnson" wrote:

Need help writing code for a month change event. At the beginning of
each month, the user will enter the new month's number (i.e. Feb = 2,
Mar = 3, etc) and the macro will go to the previous month's tab (i.e.
user selects 2 for "Feb", macro would go to "Jan" tab), search in
Column "A" for any and all cells that have the word "cancellation" and
copy the data in columns B-K to the "Summary" tab in the next blank
row, starting in column B. Would also be very nice to have it
automatically fill in the month name in Column A of the Summary Tab
(i.e. if user selects month 2, the month to insert in column A would
be "January"). If anyone can help I'd appreaciate it.
Thanks!