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

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

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

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

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

Sub LFmacro()

Dim wbk1 as Workbook
Dim wbk2 as Workbook
Dim Source as Range
Dim Dest as Range
Dim i as Long

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
i = -1
do while source.offset(i,0).Value = source
i = i + 1
Loop
set source = source.offset(i + 1,0).Resize(-i).EntireRow
Source.Copy Dest
End Sub

--
Regards,
Tom Ogilvy


"Meltad" wrote in message
...
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

Hi Tom,
Thanks for that, I tried to run it but got an error on this line:

"Run time error 92, object variable or With block variable not set"

Set Dest = wbk1.Worksheets(1).Cells(Rows.Count, 3).End(xlUp)(2).EntireRow

Not sure what I've missed...



"Tom Ogilvy" wrote:

Sub LFmacro()

Dim wbk1 as Workbook
Dim wbk2 as Workbook
Dim Source as Range
Dim Dest as Range
Dim i as Long

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
i = -1
do while source.offset(i,0).Value = source
i = i + 1
Loop
set source = source.offset(i + 1,0).Resize(-i).EntireRow
Source.Copy Dest
End Sub

--
Regards,
Tom Ogilvy


"Meltad" wrote in message
...
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

I think Tom meant wbk2:

Set Dest = wbk2.Worksheets(1).Cells(Rows.Count, 3).End(xlUp)(2).EntireRow


"Meltad" wrote:

Hi Tom,
Thanks for that, I tried to run it but got an error on this line:

"Run time error 92, object variable or With block variable not set"

Set Dest = wbk1.Worksheets(1).Cells(Rows.Count, 3).End(xlUp)(2).EntireRow

Not sure what I've missed...



"Tom Ogilvy" wrote:

Sub LFmacro()

Dim wbk1 as Workbook
Dim wbk2 as Workbook
Dim Source as Range
Dim Dest as Range
Dim i as Long

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
i = -1
do while source.offset(i,0).Value = source
i = i + 1
Loop
set source = source.offset(i + 1,0).Resize(-i).EntireRow
Source.Copy Dest
End Sub

--
Regards,
Tom Ogilvy


"Meltad" wrote in message
...
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

That line hasn't changed and you said it worked in the previous version of
the macro.

demo't from the immediate window:

set dest = wbk1.Worksheets(1).Cells(rows.count,3).End(xlup).E ntireRow
? dest.Address
$90:$90

--
Regards,
Tom Ogilvy


"Meltad" wrote in message
...
Hi Tom,
Thanks for that, I tried to run it but got an error on this line:

"Run time error 92, object variable or With block variable not set"

Set Dest = wbk1.Worksheets(1).Cells(Rows.Count, 3).End(xlUp)(2).EntireRow

Not sure what I've missed...



"Tom Ogilvy" wrote:

Sub LFmacro()

Dim wbk1 as Workbook
Dim wbk2 as Workbook
Dim Source as Range
Dim Dest as Range
Dim i as Long

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
i = -1
do while source.offset(i,0).Value = source
i = i + 1
Loop
set source = source.offset(i + 1,0).Resize(-i).EntireRow
Source.Copy Dest
End Sub

--
Regards,
Tom Ogilvy


"Meltad" wrote in message
...
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

Sorry about this Tom,
On your earlier post you gave 2 examples of codes to use to copy the latest
date, I used the top one (this worked for one line only) - the second one you
posted containing this line where I get the error but doesn't work for me. I
changed to wbk1 to wbk2 as cush suggested but still get an error on another
line a bit further down:
Do While Source.Offset(i, 0).Value = Source
(application defined or object defined error??)
??


"Tom Ogilvy" wrote:

That line hasn't changed and you said it worked in the previous version of
the macro.

demo't from the immediate window:

set dest = wbk1.Worksheets(1).Cells(rows.count,3).End(xlup).E ntireRow
? dest.Address
$90:$90

--
Regards,
Tom Ogilvy


"Meltad" wrote in message
...
Hi Tom,
Thanks for that, I tried to run it but got an error on this line:

"Run time error 92, object variable or With block variable not set"

Set Dest = wbk1.Worksheets(1).Cells(Rows.Count, 3).End(xlUp)(2).EntireRow

Not sure what I've missed...



"Tom Ogilvy" wrote:

Sub LFmacro()

Dim wbk1 as Workbook
Dim wbk2 as Workbook
Dim Source as Range
Dim Dest as Range
Dim i as Long

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
i = -1
do while source.offset(i,0).Value = source
i = i + 1
Loop
set source = source.offset(i + 1,0).Resize(-i).EntireRow
Source.Copy Dest
End Sub

--
Regards,
Tom Ogilvy


"Meltad" wrote in message
...
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

Sub LFmacro()

Dim nRows As Long
Dim i as Long
Dim Source as Range

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))
i = 1
Do While Cells(nrows, 3).Offset(-i, 0).Value = Cells(nrows, 3).Value
i = i + 1
Loop
Set Source = Cells(nrows, 3).Offset(-1 * (i - 1), 0).Resize(i).EntireRow
Source.Copy
Windows("LFmacro.XLS").Activate
cells(rows.count,3).End(xlup).offset(1,-2).Select
ActiveSheet.Paste

End Sub

--
Regards,
Tom Ogilvy


"Meltad" wrote in message
...
Sorry about this Tom,
On your earlier post you gave 2 examples of codes to use to copy the
latest
date, I used the top one (this worked for one line only) - the second one
you
posted containing this line where I get the error but doesn't work for me.
I
changed to wbk1 to wbk2 as cush suggested but still get an error on
another
line a bit further down:
Do While Source.Offset(i, 0).Value = Source
(application defined or object defined error??)
??


"Tom Ogilvy" wrote:

That line hasn't changed and you said it worked in the previous version
of
the macro.

demo't from the immediate window:

set dest = wbk1.Worksheets(1).Cells(rows.count,3).End(xlup).E ntireRow
? dest.Address
$90:$90

--
Regards,
Tom Ogilvy


"Meltad" wrote in message
...
Hi Tom,
Thanks for that, I tried to run it but got an error on this line:

"Run time error 92, object variable or With block variable not set"

Set Dest = wbk1.Worksheets(1).Cells(Rows.Count,
3).End(xlUp)(2).EntireRow

Not sure what I've missed...



"Tom Ogilvy" wrote:

Sub LFmacro()

Dim wbk1 as Workbook
Dim wbk2 as Workbook
Dim Source as Range
Dim Dest as Range
Dim i as Long

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
i = -1
do while source.offset(i,0).Value = source
i = i + 1
Loop
set source = source.offset(i + 1,0).Resize(-i).EntireRow
Source.Copy Dest
End Sub

--
Regards,
Tom Ogilvy


"Meltad" wrote in message
...
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

Hi Tom,
Thanks again! This code works through with no errors but the loop part
doesn't seem to do what I expected... I still only get 1 row pasted across to
the new workbook - it doesn't pick up all instances of that date. Also please
can you explain how this is supposed to work? I can't understand how it will
select that latest date only?? Thank you, sorry to be a pain!


"Tom Ogilvy" wrote:

Sub LFmacro()

Dim nRows As Long
Dim i as Long
Dim Source as Range

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))
i = 1
Do While Cells(nrows, 3).Offset(-i, 0).Value = Cells(nrows, 3).Value
i = i + 1
Loop
Set Source = Cells(nrows, 3).Offset(-1 * (i - 1), 0).Resize(i).EntireRow
Source.Copy
Windows("LFmacro.XLS").Activate
cells(rows.count,3).End(xlup).offset(1,-2).Select
ActiveSheet.Paste

End Sub

--
Regards,
Tom Ogilvy


"Meltad" wrote in message
...
Sorry about this Tom,
On your earlier post you gave 2 examples of codes to use to copy the
latest
date, I used the top one (this worked for one line only) - the second one
you
posted containing this line where I get the error but doesn't work for me.
I
changed to wbk1 to wbk2 as cush suggested but still get an error on
another
line a bit further down:
Do While Source.Offset(i, 0).Value = Source
(application defined or object defined error??)
??


"Tom Ogilvy" wrote:

That line hasn't changed and you said it worked in the previous version
of
the macro.

demo't from the immediate window:

set dest = wbk1.Worksheets(1).Cells(rows.count,3).End(xlup).E ntireRow
? dest.Address
$90:$90

--
Regards,
Tom Ogilvy


"Meltad" wrote in message
...
Hi Tom,
Thanks for that, I tried to run it but got an error on this line:

"Run time error 92, object variable or With block variable not set"

Set Dest = wbk1.Worksheets(1).Cells(Rows.Count,
3).End(xlUp)(2).EntireRow

Not sure what I've missed...



"Tom Ogilvy" wrote:

Sub LFmacro()

Dim wbk1 as Workbook
Dim wbk2 as Workbook
Dim Source as Range
Dim Dest as Range
Dim i as Long

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
i = -1
do while source.offset(i,0).Value = source
i = i + 1
Loop
set source = source.offset(i + 1,0).Resize(-i).EntireRow
Source.Copy Dest
End Sub

--
Regards,
Tom Ogilvy


"Meltad" wrote in message
...
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

It finds the last date by counting the number of entries.

nrows = Application.CountA(Columns(3))

assumes the latest date is the last date.

--
Regards,
Tom Ogilvy



"Meltad" wrote in message
...
Hi Tom,
Thanks again! This code works through with no errors but the loop part
doesn't seem to do what I expected... I still only get 1 row pasted across
to
the new workbook - it doesn't pick up all instances of that date. Also
please
can you explain how this is supposed to work? I can't understand how it
will
select that latest date only?? Thank you, sorry to be a pain!


"Tom Ogilvy" wrote:

Sub LFmacro()

Dim nRows As Long
Dim i as Long
Dim Source as Range

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))
i = 1
Do While Cells(nrows, 3).Offset(-i, 0).Value = Cells(nrows, 3).Value
i = i + 1
Loop
Set Source = Cells(nrows, 3).Offset(-1 * (i - 1),
0).Resize(i).EntireRow
Source.Copy
Windows("LFmacro.XLS").Activate
cells(rows.count,3).End(xlup).offset(1,-2).Select
ActiveSheet.Paste

End Sub

--
Regards,
Tom Ogilvy


"Meltad" wrote in message
...
Sorry about this Tom,
On your earlier post you gave 2 examples of codes to use to copy the
latest
date, I used the top one (this worked for one line only) - the second
one
you
posted containing this line where I get the error but doesn't work for
me.
I
changed to wbk1 to wbk2 as cush suggested but still get an error on
another
line a bit further down:
Do While Source.Offset(i, 0).Value = Source
(application defined or object defined error??)
??


"Tom Ogilvy" wrote:

That line hasn't changed and you said it worked in the previous
version
of
the macro.

demo't from the immediate window:

set dest = wbk1.Worksheets(1).Cells(rows.count,3).End(xlup).E ntireRow
? dest.Address
$90:$90

--
Regards,
Tom Ogilvy


"Meltad" wrote in message
...
Hi Tom,
Thanks for that, I tried to run it but got an error on this line:

"Run time error 92, object variable or With block variable not set"

Set Dest = wbk1.Worksheets(1).Cells(Rows.Count,
3).End(xlUp)(2).EntireRow

Not sure what I've missed...



"Tom Ogilvy" wrote:

Sub LFmacro()

Dim wbk1 as Workbook
Dim wbk2 as Workbook
Dim Source as Range
Dim Dest as Range
Dim i as Long

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
i = -1
do while source.offset(i,0).Value = source
i = i + 1
Loop
set source = source.offset(i + 1,0).Resize(-i).EntireRow
Source.Copy Dest
End Sub

--
Regards,
Tom Ogilvy


"Meltad" wrote in message
...
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