Unable to select the cell contains today's date

Question

I am consolidating the daily report sent by my team in a single file named as "Master file" it will have each sheet separately for each of my team members. i need to find the cell contains today's date in a report sent by my team member and copy the corresponding cells and paste it in the "Master file" Here is the code

Sub Copy_data()
    Sheets("Daily Report").Select
    Range("A7").Select
    Dim mydate As Date
    mydate = Range("B1")
    For i = 1 To 4 'this is sample actually i have 38 sheets
    Dim filename As Variant
    ActiveCell.Offset(1, 0).Select
    filename = ActiveCell.Value
    Workbooks.Open "C:\Users\test\Desktop\AP\" & filename
    Application.Wait (Now + TimeValue("0:00:02"))
    Sheets("Dashboard").Select
    Cells.Find(What:=mydate, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate ' this is where i get an error as "object variable or with block variable not set"

    ActiveCell.Offset(0, 2).Select
    Dim currentcell As Integer
    currentcell = ActiveCell.Row
    Range(Selection, Cells(currentcell, 10)).Copy
    Windows("Agent Performance.xls").Activate
    Dim sheetname As String
    sheetname = ActiveCell.Offset(0, 1).Value
    Sheets(sheetname).Select

    'Here again i have to find the cell with today's date and paste the data which i copied    
    Next i

End Sub

Note :- It was working fine in the earlier stage. After making few changes in the format and appearance, also added all the sheets in the "master file" after then i am getting this error !! Also i am beginner to VBA, kindly pardon me for any flaws.

On the same line " Cells.Find(What:=mydate, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate" — David, Jul 18 '17 at 16:45
@David what is the error code and error name provided by the system? — Cyril, Jul 18 '17 at 16:49
[You should avoid using `.Activate`/`.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — BruceWayne, Jul 18 '17 at 17:01
I believe this is what you meant " Cells.Find(What:=JesusB, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Select" but sorry no luck it throws the same error !! — David, Jul 18 '17 at 17:05

score 0 · Accepted Answer · answered Jul 18 '17 at 17:21

Going out on a limb, I tried to fix up your code and avoid all the .Select/.Activate, which can cause some headaches.

In your OP I don't see where you go to paste, so made an educated guess at the end, and noted as such.

Step through this with F8 to make sure it's working properly, as you can then follow it one line at a time.

Sub Copy_data()
Dim newWB As Workbook, currentWB As Workbook, agentWB As Workbook
Dim dailyWS As Worksheet, dashWS As Worksheet
Dim i       As Long
Dim foundCell As Range
Dim currentcell As Integer
Dim destSheetname As String


Set currentWB = ThisWorkbook
Set dailyWS = currentWB.Sheets("Daily Report")
Dim mydate  As Date
mydate = dailyWS.Range("B1")
For i = 1 To 4               'this is sample actually i have 38 sheets
    Dim filename As Variant
    filename = dailyWS.Range("A7").Offset(1, 0).Value
    Set newWB = Workbooks.Open("C:\Users\test\Desktop\AP\" & filename)
    Application.Wait (Now + TimeValue("0:00:02"))
    Set dashWS = newWB.Sheets("Dashboard")
    Set foundCell = dashWS.Cells.Find(What:=mydate, After:=ActiveCell, LookIn:=xlFormulas, _
                                      LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                      MatchCase:=False, SearchFormat:=False)

    currentcell = foundCell.Offset(0, 2).Row
    dashWS.Range(foundCell.Offset(0, 2), dashWS.Cells(currentcell, 10)).Copy
    Set agentWB = Workbooks("Agent Performance.xls")
    destSheetname = agentWB.Sheets(ActiveSheet).Range("A1").Offset(0, 1).Value 'Do you know the activesheet name? If so use it here instead.
    agentWB.Sheets(destSheetname).Activate
    ''' Is this where you want to paste??
    agentWB.Sheets(destSheetname).Range("A1").Paste

    'Here again i have to find the cell with today's date and paste the data which i copied
Next i

End Sub

Thank you so much Bruce for your extended help !!! I really appreciate it !! It worked !!! — David, Jul 19 '17 at 14:33
@David - Glad it worked! FYI I think what I did was mainly get rid of the `.Select`/`.Activate` and used variables to work directly with the data. I believe your main error was you can't do `.Select` with `Cells.Find()`. That's why I set a Range variable to be equal to the `.Find()`. You *could* after that line do `foundCell.Select` if you really wanted to select it, FYI. But there's usually not a need to actually select a cell (although I do use `.Select` often when debugging with `F8` to make sure the right cells are being used). — BruceWayne, Jul 19 '17 at 14:35
(@David - if it did work, you can flag it as "the answer" by clicking the check mark left of the post, under the up/down arrows). — BruceWayne, Jul 19 '17 at 14:36

Unable to select the cell contains today's date

1 Answers1