Copying latest date from weekly workbook
This is great thanks Tom but I need to copy over all rows with that date not
just the one. How can I amend the code to pick up all instances of the most
recent date?
"Tom Ogilvy" wrote:
Sub LFmacro()
Dim nRows As Long
ChDir "G:\Lou French macro"
Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"
'copy latest date from podnondel workbook
'colour rows red
nrows = Application.CountA(columns(3))
rows(nrows).copy
Windows("LFmacro.XLS").Activate
cells(rows.count,3).End(xlup).offset(1,-2).Select
ActiveSheet.Paste
End Sub
or
Sub LFmacro()
Dim wbk1 as Workbook
Dim wbk2 as Workbook
Dim Source as Range
Dim Dest as Range
Set wbk2 = Workbooks("LFmacro.XLS")
Set Dest = wbk1.Worksheets(1).Cells(rows.count,3).End(xlUp)(2 ).EntireRow
ChDir "G:\Lou French macro"
Set wbk1=Workbooks.Open( Filename:="G:\Lou French macro\podnondel.CSV")
Set Source = wbk1.Worksheets(1).Cells(rows.count,3).End(xlup).E ntireRow
'copy latest date from podnondel workbook
'colour rows red
Source.Copy Dest
End Sub
Change Worksheets(1) to reference the proper sheet in LFmacro.xls
--
Regards,
Tom Ogilvy
"Meltad" wrote in message
...
Thanks,
My dates are in column C, so Inplayed around with your macro ( replaced 1
with 3) but wasn't sure how this was working. This is what I've got so far
Sub LFmacro()
Dim nRows As Long
ChDir "G:\Lou French macro"
Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"
'copy latest date from podnondel workbook
'colour rows red
Windows("LFmacro.XLS").Activate
Range("A2").Select
ActiveSheet.Paste
End Sub
So I need to know how to slot your latest date part into the middle!!
Would
this work or do I abandon my part and use all of yours??
Thanks
"cush" wrote:
Assuming the dates are in col A you could try some thing like:
Sub MoveData()
Dim wbk1 as Workbook
Dim wbk2 as Workbook
Dim Source as Range
Dim Dest as Range
Set wbk1 = ActiveWorkbook
Set Source = wbk1.Sheets(1).Range("A65536").End(xlUp).EntireRow
Set wbk2 = Workbooks("MyOtherBook")
Set Dest = wbk2.Sheets(1).Range("A65536").End(xlUp)
Source.Copy Dest
End Sub
"Meltad" wrote:
Hi everyone,
I'm trying to write a macro to copy data from one workbook to another.
I
have opened the first workbook which has a weeks worth of dates and
data, and
need to identify and copy the latest date on that workbook and paste
into the
new workbook. The data is sorted by date so the latest date is always
at the
bottom. Is there a way of selecting the rows for the most recent date
only??
I hope someone can help me!
Thanks, Mel
|