Hi,
I'm writing a macro that will import data from one workbook to another, based on yesterdays date. I have the import functionality working, and I've been playing all afternoon, but I can't find a way to find yesterdays date in a range and use that row number as the row offset value in this line:
Imprt.Offset(14, 0).Offset(0, 1).Value = c.Offset(0, 5).Value
Rather than having a set row offset value (in this case, 14) I would like to use the find function to lookup yesterdays date in range "B50:B80" in the worksheet I've designated as "sh1" and return the row number of the cell that has yesterdays date and use this value as the row offset value, replacing the hard coded 14 that is in there currently. The date values in the range are formatted as per Date Format.jpg.
My code is below:
Sub Import_Trucking()
Dim wb As Workbook, wb2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim strPath As String, strFolder As String, strYear As String, strFname As String, strSheetName As Double
Dim lr As Long, c As Range, Imprt As Range, rng As Range
Set wb = ThisWorkbook
Set sh1 = ActiveSheet
strPath = "G:\Mining_Geology\06. Production\01. Daily Production\03. Truck Haulage\"
strYear = Format(Date - 1, "yyyy") & "\"
strFolder = strPath & strYear
strFname = "*" & Format(Date - 1, "mmm") & ".xlsx"
strSheetName = Format(Date - 1, "d")
ChDrive "g"
ChDir strFolder
Workbooks.Open Filename:=(strFolder & strFname)
Sheets(strSheetName).Select
Set wb2 = ActiveWorkbook
Set sh2 = Sheets(strSheetName)
Set rng = sh2.Range("C38:C47")
For Each c In rng
Set Imprt = sh1.Range("C47:CL47").Find(c.Value, , xlValues)
If Not Imprt Is Nothing Then
Imprt.Offset(14, 0).Offset(0, 1).Value = c.Offset(0, 5).Value
Imprt.Offset(14, 0).Offset(0, 3).Value = c.Offset(0, 6).Value
End If
Next
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub
Many thanks,
Dan
Bookmarks