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.

On Mar 22, 9:21 pm, wangww wrote:
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

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

On Sat, 22 Mar 2008 18:21:01 -0700, wangww
wrote:

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.

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" wrote:

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

On Sat, 22 Mar 2008 18:21:01 -0700, wangww
wrote:

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.

Good to hear.

Thanks for the feedback.

Gord

On Mon, 24 Mar 2008 00:47:01 -0700, wangww
wrote:

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" wrote:

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

On Sat, 22 Mar 2008 18:21:01 -0700, wangww
wrote:

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.