copy latest price from sheet1 to sheet2 using VBA

[wangww]

Hi
Many record in the worksheet1,you can see below:
Date Products Price
2008-02-02 ABC 34
2008-02-03 ABC 36
2008-02-05 ABC 35

I have another sheet which named worksheet2.
Now I want to copy latest price from worksheet1 to worksheet2 by VBA.

Thanks in advance for your help.

 

[cht13er]

Hi
Many record in the worksheet1,you can see below:
Date Products Price
2008-02-02 ABC 34
2008-02-03 ABC 36
2008-02-05 ABC 35

I have another sheet which named worksheet2.
Now I want to copy latest price from worksheet1 to worksheet2 by VBA.

Thanks in advance for your help.

Try running this macro in a module:

Private Sub CopyNewest()

dim dtLatestDate as Date

sheets("Sheet1").activate

dtlatestdate = cells(1,1).value
cells(2,1).select

do until activecell = ""
if activecell > dtlatestdate then
dtlatestdate = activecell
end if
activecell.offset(1,0).select
loop

cells(1,1).select
do until activecell = dtlatestdate
activecells.offset(1,0).select
loop

sheets("Sheet2").Cells(1,1) = activecell.offset(0,2).value

End Sub

 

[Gord Dibben]

Will the dates always be in chronological order per your example?

Sub findbottom_paste()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp) _
.Offset(0, 2)
Set rng2 = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
rng1.Copy Destination:=rng2
End Sub

Will copy number from Price list corresponding to last entry in Date column.

Paste to first blank cell below existing data in Column A of Sheet2


Gord Dibben MS Excel MVP

 

[wangww]

Hi Gord Dibben
The dates always be in chronological order.

Your code is clear.
It is easy to understand.
It give me a great help.
I love you!

 

[Gord Dibben]

Good to hear.

Thanks for the feedback.

Gord

Hi Gord Dibben
The dates always be in chronological order.

Your code is clear.
It is easy to understand.
It give me a great help.
I love you!