Finding one column head containg current date and filtering

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.

ABCDEFGHI
11/01/201511/02/201511/03/2015
Sr.NoGradeEmployee NameTimeRemarksTimeRemarksTimeRemarks
1 I Mr. Smithpl
2 II Ms. Smithodl
3 II Mr. Johnpl

<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 workbook name is "Late Records".

My Questions are here -

  1. How could I correct this code???
  2. If it was solved, I will filter on “pl” and “odl” words. For this, I can do.
  3. 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.
I am sorry if my post was too long and complicated and if there were any words that are not polite. English is my second language. If I made any mistakes, my apologies come here.
With my sincerely and advance thanks,
Ko Htut
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Forum statistics

Threads
1,214,870
Messages
6,122,021
Members
449,060
Latest member
LinusJE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top