copy latest price from sheet1 to sheet2 using VBA

W

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.
 
C

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
 
G

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
 
W

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!
 
G

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top