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
auto copy the last updated month from Excel file 1 to Excel file 2
-
- 4StarLounger
- Posts: 456
- Joined: 05 Dec 2016, 13:48
auto copy the last updated month from Excel file 1 to Excel file 2
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: auto copy the last updated month from Excel file 1 to Excel file 2
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.
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
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 456
- Joined: 05 Dec 2016, 13:48
Re: auto copy the last updated month from Excel file 1 to Excel file 2
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
could u please take a look , sorry for the hassle
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: auto copy the last updated month from Excel file 1 to Excel file 2
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:
Warning: Book3 has Alfalfa, book 4 has Alfa Alfa. That is not the same!
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
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 456
- Joined: 05 Dec 2016, 13:48
Re: auto copy the last updated month from Excel file 1 to Excel file 2
im sorry for the misleading, the code work perfectly now, thanks a lot