Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro help
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(ISNUM BER(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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro help
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro help
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 "Greg Wilson" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |