macro help

[yshridhar]

Hello all
I maintain school database in excel. I am entering the students attendance
using excel using the macro. The work book contain sheets - Data, months
(Jan, Feb,..)

Sub attendance()

Sheets("data").Select
Range("e3").Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets("Jan").Select
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets("data").Select
Range("a3").Select
End Sub

The data.sheet where i enter absenties list in A:B columns. In column D -
student ID, and E - the formula
=IF(ISNUMBER(MATCH(D3,$A$3:$A$100,0)),"a",IF(ISNUMBER(MATCH(D3,$B$3:$B$100,0)),0.5,1))
if the student is absent = "a", present half day = 0.5, else = 1
The column E is copied via macro to Jan-sheet to C3.
What I want is if i enter Date in Data.C2, the macro has to copy the Column
E to the respective month sheet and Day column.
Sheets("Jan").Select --- get the month from date - Data.c2
Range("C3").Select ---- Get the date - Data.c2
Any suggestions.
Thanks to all in advance
With warm regards
Sridhar

 

[Greg Wilson]

This assumes that you want to paste the results from column E of sheet Data
to the sheet corresponding to the month value entered in cell C2 of sheet
Data. The destination column corresponds with the day value of the date in C2
- e.g. if March 10, 2007 is in cell C2 of sheet Data, then the column E data
should be pasted to the 10th column of sheet "Mar". You may wish to apply an
offset for the column selection if this isn't correct.

Sub k()
Dim r As Range
Dim m As Integer, d As Integer
Dim msg As String, ttl As String
Dim sheetnames As Variant

sheetnames = Array("Jan", "Feb", "Mar", "Apr", "May", _
"Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

With Sheets("Data")
With .Range("C2")
If Not IsDate(.Value) Then
msg = "Error: Date not entered in cell C2"
ttl = "Student Attendance"
MsgBox msg, vbCritical, ttl
Exit Sub
End If
m = Month(.Value)
d = Day(.Value)
End With
Set r = .Range(.Cells(3, 5), .Cells(3, 5).End(xlDown))
End With
With Sheets(sheetnames(m - 1)).Cells(3, d).Resize(r.Count)
.Value = r.Value
End With
Set r = Nothing
End Sub

Greg

 

[yshridhar]

Thanks alot Greg. It is what i need.
I made two modifications to fit to my data.
1. d = Day(.Value) + 2
2. I cleared the contents Data.A2:B200
My heart felt thanks Greg.
With warm Regards
Sreedhar