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 <wan...@discussions.microsoft.com> 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 <(E-Mail Removed)>
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 <(E-Mail Removed)>
> 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 <(E-Mail Removed)>
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 <(E-Mail Removed)>
>> 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.
>>
>>