Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy data based on month? | Excel Discussion (Misc queries) | |||
calculate a date based on specified day of the month | Excel Discussion (Misc queries) | |||
Automatically update a cell with a date based on anther cells date | Excel Discussion (Misc queries) | |||
figure to date totals based on month | Excel Worksheet Functions | |||
Current date formula based on month | Excel Discussion (Misc queries) |