Automatically Copy Row based on Date (Month)
 

I have a spreadsheet where my employees keep a list of their customers
I have a column on this spreadsheet has the customer's date of birth
What I am trying to do is copy the row to another sheet based on th
month of the birthday. Basically I have 13 sheets, 1 for the clien
list, and 1 for each month. Please help. I found something that i
similar, but don't know how to use a date range (code below)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim LastRow As Object
' Set your Copy To range here
Set LastRow = Sheets("Sheet2").Range("A65536").End(xlUp)
' Only look at single cell changes
' If Target.Count 1 Then Exit Sub
' Set your change range here
Set rng = Range("A:A")
' only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Set your condition here
If Target = "ABC" Then Target.EntireRow.Copy LastRow.Offset(1
0)
End Su

--
hawkins_c
-----------------------------------------------------------------------
hawkins_cm's Profile: http://www.excelforum.com/member.php...fo&userid=1681
View this thread: http://www.excelforum.com/showthread.php?threadid=32014

Automatically Copy Row based on Date (Month)
 

Hi
You'll need to read down the client sheet copying the rows to other sheets
based on dates OK! - here's some code that'll help you understand how to
extract the month from a date using datepart. Let me know if you need more ...

Sub datecheck()

Dim TheDate As Date ' Declare variables.
Dim Msg
Dim mnth As Integer

TheDate = InputBox("Enter a date:")
mnth = DatePart("m", TheDate)
Msg = "Month: " & DatePart("m", TheDate)
MsgBox Msg

Select Case mnth

Case 1: MsgBox "copy to Jan sheet"
Case 2: MsgBox "copy to Feb sheet"
Case 3: MsgBox "copy to Mar sheet"

Case Else

MsgBox "After march"

End Select

End Sub



"hawkins_cm" wrote:


I have a spreadsheet where my employees keep a list of their customers.
I have a column on this spreadsheet has the customer's date of birth.
What I am trying to do is copy the row to another sheet based on the
month of the birthday. Basically I have 13 sheets, 1 for the client
list, and 1 for each month. Please help. I found something that is
similar, but don't know how to use a date range (code below)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim LastRow As Object
' Set your Copy To range here
Set LastRow = Sheets("Sheet2").Range("A65536").End(xlUp)
' Only look at single cell changes
' If Target.Count 1 Then Exit Sub
' Set your change range here
Set rng = Range("A:A")
' only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Set your condition here
If Target = "ABC" Then Target.EntireRow.Copy LastRow.Offset(1,
0)
End Sub


--
hawkins_cm
------------------------------------------------------------------------
hawkins_cm's Profile: http://www.excelforum.com/member.php...o&userid=16819
View this thread: http://www.excelforum.com/showthread...hreadid=320146