Tet Htut Naing
Board Regular
- Joined
- Mar 28, 2015
- Messages
- 101
Dear All,
I am just a beginner of learning excel VBA/Macro and now I am tired of searching solution for my case. I am working for a local NGO, running for peace in Myanmar country.
In my office, I have to handle with staff records, including office attendance record. If one staff is late for particular day, I would enter his late record into my Late Record Workbook. I have 12 worksheets in this workbook, one sheet for one month. In the table below, you can see the exact way of recording, supposing today is 11/02/2015.
<tbody>
</tbody>
pl = Personal Late, odl = On-Duty Late
Here, I want to filter on the current date (11/02/2015) column. I have a macro created by Mr. JackDanIce (my appreciation), one of this forum member, as below.
In the above macro, the problem lies on
My workbook name is "Late Records".
My Questions are here -
With my sincerely and advance thanks,
Ko Htut
I am just a beginner of learning excel VBA/Macro and now I am tired of searching solution for my case. I am working for a local NGO, running for peace in Myanmar country.
In my office, I have to handle with staff records, including office attendance record. If one staff is late for particular day, I would enter his late record into my Late Record Workbook. I have 12 worksheets in this workbook, one sheet for one month. In the table below, you can see the exact way of recording, supposing today is 11/02/2015.
A | B | C | D | E | F | G | H | I |
11/01/2015 | 11/02/2015 | 11/03/2015 | ||||||
Sr.No | Grade | Employee Name | Time | Remarks | Time | Remarks | Time | Remarks |
1 | I | Mr. Smith | pl | |||||
2 | II | Ms. Smith | odl | |||||
3 | II | Mr. John | pl |
<tbody>
</tbody>
pl = Personal Late, odl = On-Duty Late
Here, I want to filter on the current date (11/02/2015) column. I have a macro created by Mr. JackDanIce (my appreciation), one of this forum member, as below.
Code:
Sub Macro1()
Dim wsSrc As Excel.Worksheet
Dim rng As Excel.Range
Dim LC As Long
Dim LR As Long
Dim x As Long
Set wsSrc = ThisWorkbook.Sheets("Sheet1")
Application.ScreenUpdating = False
With wsSrc
If .AutoFilterMode Then .AutoFilterMode = False
LC = .Cells(4, .Columns.Count).End(xlToLeft).Column
Set rng = .Range("A4").Resize(1, LC).Find(what:=Date, LookIn:=xlValues)
If Not rng Is Nothing Then
x = rng.Column
LR = .Cells(.Rows.Count, rng.Column).End(xlUp).Row
Set rng = .Range("A4", rng).Resize(LR)
Else
MsgBox "Date not found, macro stopping", vbOKOnly
Set wsSrc = Nothing
End
End If
With rng
.AutoFilter
.AutoFilter Field:=x, Criteria1:="pl"
End With
End With
Application.ScreenUpdating = True
End Sub
In the above macro, the problem lies on
Code:
Set wsSrc = ThisWorkbook.Sheets("Sheet1")
My Questions are here -
- How could I correct this code???
- If it was solved, I will filter on “pl” and “odl” words. For this, I can do.
- After filtering, in order to be able to copy the filtered results, I want to hide column D and E. So, let’s say, I want to hide columns ranging from D to one column before current date.
With my sincerely and advance thanks,
Ko Htut