auto copy the last updated month from Excel file 1 to Excel file 2

[Mohammednt0]

Good day,

is there any method to auto copy the last updated month from Excel file 1 to Excel file 2

please check the attached example file

[HansV]

It would have been better if you had provided actual sample workbooks.
Here is a macro, but you may have to modify it for the actual layout of your worksheets.

Code: Select all

Sub CopyRecent()
    Dim wbs As Workbook
    Dim wss As Worksheet
    Dim wbt As Workbook
    Dim wst As Worksheet
    Dim r As Long
    Dim n As Long
    Dim c As Long
    Application.ScreenUpdating = False
    ' Change the names of the workbooks and worksheets as needed
    Set wbs = Workbooks("Book3") 'Source workbook (File1)
    Set wss = wbs.Worksheets(1)
    Set wbt = Workbooks("Book4") ' Target workbook (File2)
    Set wst = wbt.Worksheets(1)
    n = wst.Range("A1").End(xlDown).Row
    For c = 12 To 1 Step -1  ' Step backwards through the months
        If Application.CountIf(wss.Cells(2, c + 1).Resize(n - 1), ">0") > 0 Then
            ' Found last column with non-zero data
            Exit For
        End If
    Next c
    For r = 2 To n
        wst.Range("D" & r).Value = Application.VLookup(wst.Range("B" & r).Value, _
            wss.Range("A1:M" & n), c + 1, False)
    Next r
    Application.ScreenUpdating = True
End Sub

[Mohammednt0]

i tried to modify the code but no success , attached are the actual layout of my worksheets
could u please take a look , sorry for the hassle

[HansV]

That changes things considerably. You should not have asked your question the way you did in your first post - it is misleading!
Here is a new version:

Code: Select all

Sub CopyRecent()
    Dim wbs As Workbook
    Dim wss As Worksheet
    Dim wbt As Workbook
    Dim wst As Worksheet
    Dim r As Long
    Dim n As Long
    Dim c As Long
    Dim rng As Range
    Application.ScreenUpdating = False
    ' Change the names of the workbooks and worksheets as needed
    Set wbs = Workbooks("Book3.xlsx") 'Source workbook (File1)
    Set wss = wbs.Worksheets(1)
    n = wss.Range("A" & wss.Rows.Count).End(xlUp).Row
    Set wbt = ThisWorkbook ' Target workbook (File2)
    Set wst = wbt.Worksheets(1)
    For c = 12 To 1 Step -1  ' Step backwards through the months
        If Application.CountIf(wss.Cells(4, c + 1).Resize(n - 3), ">0") > 0 Then
            ' Found last column with non-zero data
            Exit For
        End If
    Next c
    For r = 4 To n
        Set rng = wst.Range("C:C").Find(What:=wss.Range("A" & r).Value, LookAt:=xlWhole)
        If Not rng Is Nothing Then
            rng.Offset(0, 2).Value = wss.Cells(r, c + 1).Value
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

Warning: Book3 has Alfalfa, book 4 has Alfa Alfa. That is not the same!

[Mohammednt0]

im sorry for the misleading, the code work perfectly now, thanks a lot