create new worksheet based on month change

[Mike.M]

Hi, I have a problem I hope someone can help me with. I have a worksheet for
employees to enter data. At the beginning of each month I run a macro which
copies a mastersheet to start the new month. The first column of the sheet
is for the employee to enter their name. Using code when they move to the
2nd column it automatically inserts the date and the 3rd column the time
both based on whether there is an entry in the first column. I want to call
my new worksheet macro when the date changes month. I have been trying to
use ActiveCell.Offset command to look at the date in the row above but
without success at pinpointing the change in month to call my macro. Can
anyone please help?

Mike

 

[Dave Peterson]

I'm not quite sure how the activecell fits into your code. (You don't usually
have to .select or .activate ranges to work with them.)

Option Explicit
Sub testme01()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
With Worksheets("sheet1")
FirstRow = 2 'header rows?
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = FirstRow To LastRow
If Year(.Cells(iRow, "B").Value) = Year(.Cells(iRow + 1, "B").Value) Then
If Month(.Cells(iRow, "B").Value) _
= Month(.Cells(iRow + 1, "B").Value) Then
'same month/year
Else
'different month/year
'call your new worksheet stuff
End If
End If
Next iRow
End With
End Sub

You could even just format the dates the same way and compare that:

If Format(.Cells(iRow, "B").Value, "yyyymm") _
= Format(.Cells(iRow + 1, "B").Value, "yyyymm") Then
'same
Else
'different
End If

I think you'll want to check to see if the cell has anything in it in the code.
But I wasn't sure what happens then.

 

[Mike.M]

Thanks Dave this works great when the month changes but not when the year
changes. I found when I swapped the If year and If month lines around it
ran my macro on change of year but not month. How do I make it do both?

Cheers Mike

 

[Dave Peterson]

Oops. You're right

Option Explicit
Sub testme01()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
With Worksheets("sheet1")
FirstRow = 2 'header rows?
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = FirstRow To LastRow
If Year(.Cells(iRow, "B").Value) = Year(.Cells(iRow + 1, "B").Value) _
And Month(.Cells(iRow, "B").Value) _
= Month(.Cells(iRow + 1, "B").Value) Then
'same month/year
Else
'different month/year
'call your new worksheet stuff
MsgBox iRow
End If
Next iRow
End With
End Sub


But the format version will do it right (it was ok the first time) and is less
to type!

Sorry about that!