.Find in another workbook

[Diving_Dan]

Hi all,

I've scoured the internet again after trying to adapt some code I have previously used and tried the macro recorder but am getting no joy.

I have a workbook where weekly tasks are given to employees. This is the activeworkbook where I am putting the code into. I then have a second sheet called master stored in a different path.

C3 on the activeworkbook contains a date. I am trying to open the other workbook and then find that date within column A. Once that is done I will be able to use offset to input the data from the active book but before I can get to there I need to be able to find the date.

Below is the code I have so far. The master workbook opens ok but I then get an error when the find tries to run. I get the error message object variable or with block variable not set. I'm not sure where I'm going wrong. Any help is appreciated as always.

Dan

Sub copy_to_master()
    
    Dim Var As String
    Dim wb As Workbook
    
    Var = Range("C3").Value
    
    Set wb = ThisWorkbook
    
    Workbooks.Open "C:\All DEPTS\Master.xlsx"

    Range("A:A").Find(What:=Var).Activate


End Sub

 

[GWteB]

Try something like this:

Sub Example()
    
    Dim Var As String
    Dim oWs As Worksheet
    Dim c   As Range
    
    Var = ActiveSheet.Range("C3").Value
    
    Set oWs = Workbooks.Open("C:\All DEPTS\Master.xlsx").ActiveSheet
    
    With oWs.Range("A:A")
        Set c = .Find(Var, LookIn:=xlValues, LookAt:=xlPart)
        If Not c Is Nothing Then
            Do
                ' your code to perform some action if value is found
                ' c refers to range where value is found
                
                Set c = .FindNext(c)
            Loop While Not c Is Nothing
        End If
    End With

End Sub

 

[DanteAmor]

Try this. Update the name of your sheets

Sub copy_to_master()
  Dim sDate As Date
  Dim wb As Workbook
  Dim f As Range
 
  sDate = ThisWorkbook.Sheets("Sheet1").Range("C3").Value
  Set wb = Workbooks.Open("C:\All DEPTS\Master.xlsx")
  Set f = wb.Sheets("Data").Range("A:A").Find(sDate, , xlFormulas, xlWhole)
  If Not f Is Nothing Then
    MsgBox "Date found in row: " & f.Row
  Else
    MsgBox "Date does not exist"
  End If
  wb.Close False
End Sub

 

[Diving_Dan]

Try this. Update the name of your sheets

Sub copy_to_master()
 Dim sDate As Date
  Dim wb As Workbook
  Dim f As Range

  sDate = ThisWorkbook.Sheets("Sheet1").Range("C3").Value
  Set wb = Workbooks.Open("C:\All DEPTS\Master.xlsx")
  Set f = wb.Sheets("Data").Range("A:A").Find(sDate, , xlFormulas, xlWhole)
  If Not f Is Nothing Then
    MsgBox "Date found in row: " & f.Row
  Else
    MsgBox "Date does not exist"
  End If
  wb.Close False
End Sub

Thanks Dante. This does what I want and finds the date. I'm now having another problem that I know I should be able to work out but I think it's been a long day and my brain is a bit frazzled.

What I would then like to happen is to copy data from the original workbook to this new master workbook. I have done the following to the code but cannot get the data to copy over.

Sub copy_to_master()
  Dim sDate As Date
  Dim wb As Workbook
  Dim f As Range
  Dim wb2 As Workbook
 
  sDate = ThisWorkbook.ActiveSheet.Range("C3").Value
  Set wb2 = ThisWorkbook
  Set wb = Workbooks.Open("C:\All DEPTS\Master.xlsx")
  Set f = wb.Sheets("Data").Range("A:A").Find(sDate, , xlFormulas, xlWhole)
  If Not f Is Nothing Then
  f.select
  'I am trying to then add the code to copy here. It will be from various cells on the original workbook/sheet which is why I added wb2 to the code'
    MsgBox "Date found in row: " & f.Row
  Else
    MsgBox "Date does not exist"
  End If
End Sub

 

[Diving_Dan]

So once f is selected, I would then like to offset that and put the value of C6 from the original book into f.offset( , 1 ). I hope this makes sense

 

[DanteAmor]

Try this

Sub copy_to_master()
  Dim sDate As Date
  Dim sh As Worksheet                 'original sheet
  Dim wb As Workbook                  'destination
  Dim f As Range
  
  Set sh = ThisWorkbook.ActiveSheet   'original book original sheet
  sDate = sh.Range("C3").Value
  
  Set wb = Workbooks.Open("C:\All DEPTS\Master.xlsx")
  Set f = wb.Sheets("Data").Range("A:A").Find(sDate, , xlFormulas, xlWhole)
  
  If Not f Is Nothing Then
    f.Offset(, 1).Value = sh.Range("C6").Value
    wb.Close True
  Else
    MsgBox "Date does not exist"
    wb.Close False
  End If
End Sub

 

[Diving_Dan]

Dante that is exactly it, thank you so much.

 

[DanteAmor]

Glad we could help & thanks for the feedback