Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

Hi Bill,

I tried this but got an error quite early on (on this line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

When you're on the line that breaks, go to the immediate window with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

Hi Bill,

Sorry I don't understand what you mean by this? What exactly do you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate window with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

When the code execution breaks, press the "Debug" button and it will take you
to the visual basic editor. From there, press Ctrl-G to go to the Immediate
Window. In the Immediate Window, enter this line (without the quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing enter, there
should be a result below the line you typed/pasted - let me know what that
result is. I am curious to know whether the value contained in Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly do you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate window with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I entered
"?Cells(i, 3).Value" - this is consignment number in column c but the date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to extract the
latest date data from and some of the records aren't formatted as date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and it will take you
to the visual basic editor. From there, press Ctrl-G to go to the Immediate
Window. In the Immediate Window, enter this line (without the quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing enter, there
should be a result below the line you typed/pasted - let me know what that
result is. I am curious to know whether the value contained in Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly do you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate window with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

Yes, the issue is that the code is looking in the wrong cell. I added a
variable "lngColumnDate" to hold the date column, so it is easier to modify.
See if this works better for you.

As for the CSV file, it depends on how the data looks after Excel reads it
(I'm assuming you're bring it in to Excel). It's a fairly subjective matter
that I'd most likely have to see in person to judge.


Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date
Dim lngColumnDate 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

lngColumnDate = 2

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
If (Format(Cells(i, lngColumnDate).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, lngColumnDate).EntireRow
Else
Set Source = Union(Source, Cells(i,
lngColumnDate).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub




"Meltad" wrote:

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I entered
"?Cells(i, 3).Value" - this is consignment number in column c but the date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to extract the
latest date data from and some of the records aren't formatted as date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and it will take you
to the visual basic editor. From there, press Ctrl-G to go to the Immediate
Window. In the Immediate Window, enter this line (without the quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing enter, there
should be a result below the line you typed/pasted - let me know what that
result is. I am curious to know whether the value contained in Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly do you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate window with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

Bill,

I'm still getting an error on this line
" datCheck = Format(Cells(i, 3).Value, "m/d/yyyy") "
I changed the 3 to lngColumnDate but still got an error on this line.
It must be to do with the "m/d/yyyy" part...

I have opened my CSV into Excel and all the dates are now in this format
"08/01/2006" (cell format is '*08/01/2001' - not sure what the asterisk
means!) this is dd/mm/yyy.

I'm still hopeful about using your code to solve this though!


"Bill Pfister" wrote:


Yes, the issue is that the code is looking in the wrong cell. I added a
variable "lngColumnDate" to hold the date column, so it is easier to modify.
See if this works better for you.

As for the CSV file, it depends on how the data looks after Excel reads it
(I'm assuming you're bring it in to Excel). It's a fairly subjective matter
that I'd most likely have to see in person to judge.


Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date
Dim lngColumnDate 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

lngColumnDate = 2

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
If (Format(Cells(i, lngColumnDate).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, lngColumnDate).EntireRow
Else
Set Source = Union(Source, Cells(i,
lngColumnDate).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub




"Meltad" wrote:

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I entered
"?Cells(i, 3).Value" - this is consignment number in column c but the date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to extract the
latest date data from and some of the records aren't formatted as date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and it will take you
to the visual basic editor. From there, press Ctrl-G to go to the Immediate
Window. In the Immediate Window, enter this line (without the quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing enter, there
should be a result below the line you typed/pasted - let me know what that
result is. I am curious to know whether the value contained in Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly do you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate window with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

The most likely culprit is whatever is in the "Cells(i, lngColumnDate
).Value" - check it in your immediate window to see if it is a valid entry.

The "*" indicates that the date format will not switch orders (mm/dd vs.
dd/mm) depending on the operating system.



"Meltad" wrote:

Bill,

I'm still getting an error on this line
" datCheck = Format(Cells(i, 3).Value, "m/d/yyyy") "
I changed the 3 to lngColumnDate but still got an error on this line.
It must be to do with the "m/d/yyyy" part...

I have opened my CSV into Excel and all the dates are now in this format
"08/01/2006" (cell format is '*08/01/2001' - not sure what the asterisk
means!) this is dd/mm/yyy.

I'm still hopeful about using your code to solve this though!


"Bill Pfister" wrote:


Yes, the issue is that the code is looking in the wrong cell. I added a
variable "lngColumnDate" to hold the date column, so it is easier to modify.
See if this works better for you.

As for the CSV file, it depends on how the data looks after Excel reads it
(I'm assuming you're bring it in to Excel). It's a fairly subjective matter
that I'd most likely have to see in person to judge.


Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date
Dim lngColumnDate 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

lngColumnDate = 2

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
If (Format(Cells(i, lngColumnDate).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, lngColumnDate).EntireRow
Else
Set Source = Union(Source, Cells(i,
lngColumnDate).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub




"Meltad" wrote:

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I entered
"?Cells(i, 3).Value" - this is consignment number in column c but the date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to extract the
latest date data from and some of the records aren't formatted as date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and it will take you
to the visual basic editor. From there, press Ctrl-G to go to the Immediate
Window. In the Immediate Window, enter this line (without the quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing enter, there
should be a result below the line you typed/pasted - let me know what that
result is. I am curious to know whether the value contained in Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly do you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate window with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

You're right Bill,

This line in the immediate window returns a run time 1004 object or
application defined error....


"Bill Pfister" wrote:

The most likely culprit is whatever is in the "Cells(i, lngColumnDate
).Value" - check it in your immediate window to see if it is a valid entry.

The "*" indicates that the date format will not switch orders (mm/dd vs.
dd/mm) depending on the operating system.



"Meltad" wrote:

Bill,

I'm still getting an error on this line
" datCheck = Format(Cells(i, 3).Value, "m/d/yyyy") "
I changed the 3 to lngColumnDate but still got an error on this line.
It must be to do with the "m/d/yyyy" part...

I have opened my CSV into Excel and all the dates are now in this format
"08/01/2006" (cell format is '*08/01/2001' - not sure what the asterisk
means!) this is dd/mm/yyy.

I'm still hopeful about using your code to solve this though!


"Bill Pfister" wrote:


Yes, the issue is that the code is looking in the wrong cell. I added a
variable "lngColumnDate" to hold the date column, so it is easier to modify.
See if this works better for you.

As for the CSV file, it depends on how the data looks after Excel reads it
(I'm assuming you're bring it in to Excel). It's a fairly subjective matter
that I'd most likely have to see in person to judge.


Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date
Dim lngColumnDate 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

lngColumnDate = 2

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
If (Format(Cells(i, lngColumnDate).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, lngColumnDate).EntireRow
Else
Set Source = Union(Source, Cells(i,
lngColumnDate).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub




"Meltad" wrote:

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I entered
"?Cells(i, 3).Value" - this is consignment number in column c but the date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to extract the
latest date data from and some of the records aren't formatted as date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and it will take you
to the visual basic editor. From there, press Ctrl-G to go to the Immediate
Window. In the Immediate Window, enter this line (without the quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing enter, there
should be a result below the line you typed/pasted - let me know what that
result is. I am curious to know whether the value contained in Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly do you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate window with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

In the immediate window, checking these 3 items should help to indicate where
your problem lies:
?i
?lngColumnDate
?Cells(i, lngColumnDate ).Address



"Meltad" wrote:

You're right Bill,

This line in the immediate window returns a run time 1004 object or
application defined error....


"Bill Pfister" wrote:

The most likely culprit is whatever is in the "Cells(i, lngColumnDate
).Value" - check it in your immediate window to see if it is a valid entry.

The "*" indicates that the date format will not switch orders (mm/dd vs.
dd/mm) depending on the operating system.



"Meltad" wrote:

Bill,

I'm still getting an error on this line
" datCheck = Format(Cells(i, 3).Value, "m/d/yyyy") "
I changed the 3 to lngColumnDate but still got an error on this line.
It must be to do with the "m/d/yyyy" part...

I have opened my CSV into Excel and all the dates are now in this format
"08/01/2006" (cell format is '*08/01/2001' - not sure what the asterisk
means!) this is dd/mm/yyy.

I'm still hopeful about using your code to solve this though!


"Bill Pfister" wrote:


Yes, the issue is that the code is looking in the wrong cell. I added a
variable "lngColumnDate" to hold the date column, so it is easier to modify.
See if this works better for you.

As for the CSV file, it depends on how the data looks after Excel reads it
(I'm assuming you're bring it in to Excel). It's a fairly subjective matter
that I'd most likely have to see in person to judge.


Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date
Dim lngColumnDate 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

lngColumnDate = 2

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
If (Format(Cells(i, lngColumnDate).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, lngColumnDate).EntireRow
Else
Set Source = Union(Source, Cells(i,
lngColumnDate).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub




"Meltad" wrote:

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I entered
"?Cells(i, 3).Value" - this is consignment number in column c but the date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to extract the
latest date data from and some of the records aren't formatted as date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and it will take you
to the visual basic editor. From there, press Ctrl-G to go to the Immediate
Window. In the Immediate Window, enter this line (without the quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing enter, there
should be a result below the line you typed/pasted - let me know what that
result is. I am curious to know whether the value contained in Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly do you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate window with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

Bill,
What is that with "*" ?
This is an Excel feature ?

NickHK

"Bill Pfister" ...
The most likely culprit is whatever is in the "Cells(i, lngColumnDate
).Value" - check it in your immediate window to see if it is a valid
entry.

The "*" indicates that the date format will not switch orders (mm/dd vs.
dd/mm) depending on the operating system.



"Meltad" wrote:

Bill,

I'm still getting an error on this line
" datCheck = Format(Cells(i, 3).Value, "m/d/yyyy") "
I changed the 3 to lngColumnDate but still got an error on this line.
It must be to do with the "m/d/yyyy" part...

I have opened my CSV into Excel and all the dates are now in this format
"08/01/2006" (cell format is '*08/01/2001' - not sure what the asterisk
means!) this is dd/mm/yyy.

I'm still hopeful about using your code to solve this though!


"Bill Pfister" wrote:


Yes, the issue is that the code is looking in the wrong cell. I added
a
variable "lngColumnDate" to hold the date column, so it is easier to
modify.
See if this works better for you.

As for the CSV file, it depends on how the data looks after Excel reads
it
(I'm assuming you're bring it in to Excel). It's a fairly subjective
matter
that I'd most likely have to see in person to judge.


Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date
Dim lngColumnDate 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

lngColumnDate = 2

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
If (Format(Cells(i, lngColumnDate).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, lngColumnDate).EntireRow
Else
Set Source = Union(Source, Cells(i,
lngColumnDate).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub




"Meltad" wrote:

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I entered
"?Cells(i, 3).Value" - this is consignment number in column c but the
date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to extract
the
latest date data from and some of the records aren't formatted as
date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and it
will take you
to the visual basic editor. From there, press Ctrl-G to go to the
Immediate
Window. In the Immediate Window, enter this line (without the
quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing enter,
there
should be a result below the line you typed/pasted - let me know
what that
result is. I am curious to know whether the value contained in
Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly do
you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate window
with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this
line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I
changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French
macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value,
"m/d/yyyy")

If (datLatest < datCheck) Then datLatest =
datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this
date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i,
3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another -
all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies
one row not all
instances of that date, any ideas???


"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

Not sure if it isolated to Excel, but it is a formatting aspect with Excel.

Check out the comments circled in red:
http://wcpii.com/Documents/FormatCells.JPG


"NickHK" wrote:

Bill,
What is that with "*" ?
This is an Excel feature ?

NickHK

"Bill Pfister" ...
The most likely culprit is whatever is in the "Cells(i, lngColumnDate
).Value" - check it in your immediate window to see if it is a valid
entry.

The "*" indicates that the date format will not switch orders (mm/dd vs.
dd/mm) depending on the operating system.



"Meltad" wrote:

Bill,

I'm still getting an error on this line
" datCheck = Format(Cells(i, 3).Value, "m/d/yyyy") "
I changed the 3 to lngColumnDate but still got an error on this line.
It must be to do with the "m/d/yyyy" part...

I have opened my CSV into Excel and all the dates are now in this format
"08/01/2006" (cell format is '*08/01/2001' - not sure what the asterisk
means!) this is dd/mm/yyy.

I'm still hopeful about using your code to solve this though!


"Bill Pfister" wrote:


Yes, the issue is that the code is looking in the wrong cell. I added
a
variable "lngColumnDate" to hold the date column, so it is easier to
modify.
See if this works better for you.

As for the CSV file, it depends on how the data looks after Excel reads
it
(I'm assuming you're bring it in to Excel). It's a fairly subjective
matter
that I'd most likely have to see in person to judge.


Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date
Dim lngColumnDate 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

lngColumnDate = 2

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
If (Format(Cells(i, lngColumnDate).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, lngColumnDate).EntireRow
Else
Set Source = Union(Source, Cells(i,
lngColumnDate).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub




"Meltad" wrote:

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I entered
"?Cells(i, 3).Value" - this is consignment number in column c but the
date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to extract
the
latest date data from and some of the records aren't formatted as
date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and it
will take you
to the visual basic editor. From there, press Ctrl-G to go to the
Immediate
Window. In the Immediate Window, enter this line (without the
quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing enter,
there
should be a result below the line you typed/pasted - let me know
what that
result is. I am curious to know whether the value contained in
Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly do
you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate window
with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this
line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I
changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French
macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value,
"m/d/yyyy")

If (datLatest < datCheck) Then datLatest =
datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this
date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i,
3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another -
all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies
one row not all
instances of that date, any ideas???


"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

Bill,
Which version is that ?

NickHK

"Bill Pfister" ...
Not sure if it isolated to Excel, but it is a formatting aspect with
Excel.

Check out the comments circled in red:
http://wcpii.com/Documents/FormatCells.JPG


"NickHK" wrote:

Bill,
What is that with "*" ?
This is an Excel feature ?

NickHK

"Bill Pfister" ...

The most likely culprit is whatever is in the "Cells(i, lngColumnDate
).Value" - check it in your immediate window to see if it is a valid
entry.

The "*" indicates that the date format will not switch orders (mm/dd
vs.
dd/mm) depending on the operating system.



"Meltad" wrote:

Bill,

I'm still getting an error on this line
" datCheck = Format(Cells(i, 3).Value, "m/d/yyyy") "
I changed the 3 to lngColumnDate but still got an error on this line.
It must be to do with the "m/d/yyyy" part...

I have opened my CSV into Excel and all the dates are now in this
format
"08/01/2006" (cell format is '*08/01/2001' - not sure what the
asterisk
means!) this is dd/mm/yyy.

I'm still hopeful about using your code to solve this though!


"Bill Pfister" wrote:


Yes, the issue is that the code is looking in the wrong cell. I
added
a
variable "lngColumnDate" to hold the date column, so it is easier to
modify.
See if this works better for you.

As for the CSV file, it depends on how the data looks after Excel
reads
it
(I'm assuming you're bring it in to Excel). It's a fairly
subjective
matter
that I'd most likely have to see in person to judge.


Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date
Dim lngColumnDate 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

lngColumnDate = 2

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
If (Format(Cells(i, lngColumnDate).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, lngColumnDate).EntireRow
Else
Set Source = Union(Source, Cells(i,
lngColumnDate).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub




"Meltad" wrote:

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I entered
"?Cells(i, 3).Value" - this is consignment number in column c but
the
date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to extract
the
latest date data from and some of the records aren't formatted as
date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and it
will take you
to the visual basic editor. From there, press Ctrl-G to go to
the
Immediate
Window. In the Immediate Window, enter this line (without the
quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing
enter,
there
should be a result below the line you typed/pasted - let me know
what that
result is. I am curious to know whether the value contained in
Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly
do
you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate
window
with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this
line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I
changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French
macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value,
"m/d/yyyy")

If (datLatest < datCheck) Then datLatest =
datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this
date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i,
3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another -
all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just
copies
one row not all
instances of that date, any ideas???


"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

2003, SP2


"NickHK" wrote:

Bill,
Which version is that ?

NickHK

"Bill Pfister" ...
Not sure if it isolated to Excel, but it is a formatting aspect with
Excel.

Check out the comments circled in red:
http://wcpii.com/Documents/FormatCells.JPG


"NickHK" wrote:

Bill,
What is that with "*" ?
This is an Excel feature ?

NickHK

"Bill Pfister" ...

The most likely culprit is whatever is in the "Cells(i, lngColumnDate
).Value" - check it in your immediate window to see if it is a valid
entry.

The "*" indicates that the date format will not switch orders (mm/dd
vs.
dd/mm) depending on the operating system.



"Meltad" wrote:

Bill,

I'm still getting an error on this line
" datCheck = Format(Cells(i, 3).Value, "m/d/yyyy") "
I changed the 3 to lngColumnDate but still got an error on this line.
It must be to do with the "m/d/yyyy" part...

I have opened my CSV into Excel and all the dates are now in this
format
"08/01/2006" (cell format is '*08/01/2001' - not sure what the
asterisk
means!) this is dd/mm/yyy.

I'm still hopeful about using your code to solve this though!


"Bill Pfister" wrote:


Yes, the issue is that the code is looking in the wrong cell. I
added
a
variable "lngColumnDate" to hold the date column, so it is easier to
modify.
See if this works better for you.

As for the CSV file, it depends on how the data looks after Excel
reads
it
(I'm assuming you're bring it in to Excel). It's a fairly
subjective
matter
that I'd most likely have to see in person to judge.


Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date
Dim lngColumnDate 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

lngColumnDate = 2

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
If (Format(Cells(i, lngColumnDate).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, lngColumnDate).EntireRow
Else
Set Source = Union(Source, Cells(i,
lngColumnDate).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub




"Meltad" wrote:

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I entered
"?Cells(i, 3).Value" - this is consignment number in column c but
the
date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to extract
the
latest date data from and some of the records aren't formatted as
date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and it
will take you
to the visual basic editor. From there, press Ctrl-G to go to
the
Immediate
Window. In the Immediate Window, enter this line (without the
quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing
enter,
there
should be a result below the line you typed/pasted - let me know
what that
result is. I am curious to know whether the value contained in
Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly
do
you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate
window
with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this
line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I
changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French
macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value,
"m/d/yyyy")

If (datLatest < datCheck) Then datLatest =
datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this
date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i,
3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another -
all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just
copies
one row not all
instances of that date, any ideas???


"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))

Bill,
OK. I'll try to remember that when I get my next upgrade, as I'm still on
2000.

NickHK

"Bill Pfister" ...
2003, SP2


"NickHK" wrote:

Bill,
Which version is that ?

NickHK

"Bill Pfister" ...

Not sure if it isolated to Excel, but it is a formatting aspect with
Excel.

Check out the comments circled in red:
http://wcpii.com/Documents/FormatCells.JPG


"NickHK" wrote:

Bill,
What is that with "*" ?
This is an Excel feature ?

NickHK

"Bill Pfister" ...

The most likely culprit is whatever is in the "Cells(i,
lngColumnDate
).Value" - check it in your immediate window to see if it is a valid
entry.

The "*" indicates that the date format will not switch orders (mm/dd
vs.
dd/mm) depending on the operating system.



"Meltad" wrote:

Bill,

I'm still getting an error on this line
" datCheck = Format(Cells(i, 3).Value, "m/d/yyyy") "
I changed the 3 to lngColumnDate but still got an error on this
line.
It must be to do with the "m/d/yyyy" part...

I have opened my CSV into Excel and all the dates are now in this
format
"08/01/2006" (cell format is '*08/01/2001' - not sure what the
asterisk
means!) this is dd/mm/yyy.

I'm still hopeful about using your code to solve this though!


"Bill Pfister" wrote:


Yes, the issue is that the code is looking in the wrong cell. I
added
a
variable "lngColumnDate" to hold the date column, so it is easier
to
modify.
See if this works better for you.

As for the CSV file, it depends on how the data looks after Excel
reads
it
(I'm assuming you're bring it in to Excel). It's a fairly
subjective
matter
that I'd most likely have to see in person to judge.


Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date
Dim lngColumnDate 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

lngColumnDate = 2

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
If (Format(Cells(i, lngColumnDate).Value, "m/d/yyyy")
=
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i,
lngColumnDate).EntireRow
Else
Set Source = Union(Source, Cells(i,
lngColumnDate).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count,
lngColumnDate).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub




"Meltad" wrote:

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I
entered
"?Cells(i, 3).Value" - this is consignment number in column c
but
the
date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to
extract
the
latest date data from and some of the records aren't formatted
as
date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and
it
will take you
to the visual basic editor. From there, press Ctrl-G to go
to
the
Immediate
Window. In the Immediate Window, enter this line (without
the
quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing
enter,
there
should be a result below the line you typed/pasted - let me
know
what that
result is. I am curious to know whether the value contained
in
Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What
exactly
do
you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate
window
with this
line "?Cells(i, 3).Value" and let me know what it
returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this
line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so
I
changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French
macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows =
Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value,
"m/d/yyyy")

If (datLatest < datCheck) Then datLatest
=
datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of
this
date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy")
=
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i,
3).EntireRow
Else
Set Source = Union(Source,
Cells(i,
3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count,
3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to
another -
all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just
copies
one row not all
instances of that date, any ideas???


"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 put these 3 in the immediate window, ?i returns no value (just a blank
line), same with ?lngColumnDate (these used to have a value when hovered over
in code).
?Cells(i, lngColumnDate ).Address returns an error.


"Bill Pfister" wrote:

In the immediate window, checking these 3 items should help to indicate where
your problem lies:
?i
?lngColumnDate
?Cells(i, lngColumnDate ).Address



"Meltad" wrote:

You're right Bill,

This line in the immediate window returns a run time 1004 object or
application defined error....


"Bill Pfister" wrote:

The most likely culprit is whatever is in the "Cells(i, lngColumnDate
).Value" - check it in your immediate window to see if it is a valid entry.

The "*" indicates that the date format will not switch orders (mm/dd vs.
dd/mm) depending on the operating system.



"Meltad" wrote:

Bill,

I'm still getting an error on this line
" datCheck = Format(Cells(i, 3).Value, "m/d/yyyy") "
I changed the 3 to lngColumnDate but still got an error on this line.
It must be to do with the "m/d/yyyy" part...

I have opened my CSV into Excel and all the dates are now in this format
"08/01/2006" (cell format is '*08/01/2001' - not sure what the asterisk
means!) this is dd/mm/yyy.

I'm still hopeful about using your code to solve this though!


"Bill Pfister" wrote:


Yes, the issue is that the code is looking in the wrong cell. I added a
variable "lngColumnDate" to hold the date column, so it is easier to modify.
See if this works better for you.

As for the CSV file, it depends on how the data looks after Excel reads it
(I'm assuming you're bring it in to Excel). It's a fairly subjective matter
that I'd most likely have to see in person to judge.


Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date
Dim lngColumnDate 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

lngColumnDate = 2

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
If (Format(Cells(i, lngColumnDate).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, lngColumnDate).EntireRow
Else
Set Source = Union(Source, Cells(i,
lngColumnDate).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub




"Meltad" wrote:

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I entered
"?Cells(i, 3).Value" - this is consignment number in column c but the date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to extract the
latest date data from and some of the records aren't formatted as date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and it will take you
to the visual basic editor. From there, press Ctrl-G to go to the Immediate
Window. In the Immediate Window, enter this line (without the quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing enter, there
should be a result below the line you typed/pasted - let me know what that
result is. I am curious to know whether the value contained in Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly do you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate window with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

The variables have no values because they are out of scope. You need to
check the variables while the program is running. Either set a breakpoint in
the code (inside of your "for...next" block, so it will get the "i" value) or
go into debug mode when it breaks.



"Meltad" wrote:

I put these 3 in the immediate window, ?i returns no value (just a blank
line), same with ?lngColumnDate (these used to have a value when hovered over
in code).
?Cells(i, lngColumnDate ).Address returns an error.


"Bill Pfister" wrote:

In the immediate window, checking these 3 items should help to indicate where
your problem lies:
?i
?lngColumnDate
?Cells(i, lngColumnDate ).Address



"Meltad" wrote:

You're right Bill,

This line in the immediate window returns a run time 1004 object or
application defined error....


"Bill Pfister" wrote:

The most likely culprit is whatever is in the "Cells(i, lngColumnDate
).Value" - check it in your immediate window to see if it is a valid entry.

The "*" indicates that the date format will not switch orders (mm/dd vs.
dd/mm) depending on the operating system.



"Meltad" wrote:

Bill,

I'm still getting an error on this line
" datCheck = Format(Cells(i, 3).Value, "m/d/yyyy") "
I changed the 3 to lngColumnDate but still got an error on this line.
It must be to do with the "m/d/yyyy" part...

I have opened my CSV into Excel and all the dates are now in this format
"08/01/2006" (cell format is '*08/01/2001' - not sure what the asterisk
means!) this is dd/mm/yyy.

I'm still hopeful about using your code to solve this though!


"Bill Pfister" wrote:


Yes, the issue is that the code is looking in the wrong cell. I added a
variable "lngColumnDate" to hold the date column, so it is easier to modify.
See if this works better for you.

As for the CSV file, it depends on how the data looks after Excel reads it
(I'm assuming you're bring it in to Excel). It's a fairly subjective matter
that I'd most likely have to see in person to judge.


Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date
Dim lngColumnDate 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

lngColumnDate = 2

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
If (Format(Cells(i, lngColumnDate).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, lngColumnDate).EntireRow
Else
Set Source = Union(Source, Cells(i,
lngColumnDate).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub




"Meltad" wrote:

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I entered
"?Cells(i, 3).Value" - this is consignment number in column c but the date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to extract the
latest date data from and some of the records aren't formatted as date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and it will take you
to the visual basic editor. From there, press Ctrl-G to go to the Immediate
Window. In the Immediate Window, enter this line (without the quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing enter, there
should be a result below the line you typed/pasted - let me know what that
result is. I am curious to know whether the value contained in Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly do you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate window with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

Silly me!
Got it now...

?i
1
?lngColumnDate
2
?Cells(i, lngColumnDate ).Address
$B$1

So is this failing cos row 1 is the header and not a date? How do I set it
to look at row 2 and below?


"Bill Pfister" wrote:

The variables have no values because they are out of scope. You need to
check the variables while the program is running. Either set a breakpoint in
the code (inside of your "for...next" block, so it will get the "i" value) or
go into debug mode when it breaks.



"Meltad" wrote:

I put these 3 in the immediate window, ?i returns no value (just a blank
line), same with ?lngColumnDate (these used to have a value when hovered over
in code).
?Cells(i, lngColumnDate ).Address returns an error.


"Bill Pfister" wrote:

In the immediate window, checking these 3 items should help to indicate where
your problem lies:
?i
?lngColumnDate
?Cells(i, lngColumnDate ).Address



"Meltad" wrote:

You're right Bill,

This line in the immediate window returns a run time 1004 object or
application defined error....


"Bill Pfister" wrote:

The most likely culprit is whatever is in the "Cells(i, lngColumnDate
).Value" - check it in your immediate window to see if it is a valid entry.

The "*" indicates that the date format will not switch orders (mm/dd vs.
dd/mm) depending on the operating system.



"Meltad" wrote:

Bill,

I'm still getting an error on this line
" datCheck = Format(Cells(i, 3).Value, "m/d/yyyy") "
I changed the 3 to lngColumnDate but still got an error on this line.
It must be to do with the "m/d/yyyy" part...

I have opened my CSV into Excel and all the dates are now in this format
"08/01/2006" (cell format is '*08/01/2001' - not sure what the asterisk
means!) this is dd/mm/yyy.

I'm still hopeful about using your code to solve this though!


"Bill Pfister" wrote:


Yes, the issue is that the code is looking in the wrong cell. I added a
variable "lngColumnDate" to hold the date column, so it is easier to modify.
See if this works better for you.

As for the CSV file, it depends on how the data looks after Excel reads it
(I'm assuming you're bring it in to Excel). It's a fairly subjective matter
that I'd most likely have to see in person to judge.


Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date
Dim lngColumnDate 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

lngColumnDate = 2

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
If (Format(Cells(i, lngColumnDate).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, lngColumnDate).EntireRow
Else
Set Source = Union(Source, Cells(i,
lngColumnDate).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub




"Meltad" wrote:

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I entered
"?Cells(i, 3).Value" - this is consignment number in column c but the date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to extract the
latest date data from and some of the records aren't formatted as date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and it will take you
to the visual basic editor. From there, press Ctrl-G to go to the Immediate
Window. In the Immediate Window, enter this line (without the quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing enter, there
should be a result below the line you typed/pasted - let me know what that
result is. I am curious to know whether the value contained in Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly do you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate window with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

Make your For loop start at 2 instead of 1.


"Meltad" wrote:

Silly me!
Got it now...

?i
1
?lngColumnDate
2
?Cells(i, lngColumnDate ).Address
$B$1

So is this failing cos row 1 is the header and not a date? How do I set it
to look at row 2 and below?


"Bill Pfister" wrote:

The variables have no values because they are out of scope. You need to
check the variables while the program is running. Either set a breakpoint in
the code (inside of your "for...next" block, so it will get the "i" value) or
go into debug mode when it breaks.



"Meltad" wrote:

I put these 3 in the immediate window, ?i returns no value (just a blank
line), same with ?lngColumnDate (these used to have a value when hovered over
in code).
?Cells(i, lngColumnDate ).Address returns an error.


"Bill Pfister" wrote:

In the immediate window, checking these 3 items should help to indicate where
your problem lies:
?i
?lngColumnDate
?Cells(i, lngColumnDate ).Address



"Meltad" wrote:

You're right Bill,

This line in the immediate window returns a run time 1004 object or
application defined error....


"Bill Pfister" wrote:

The most likely culprit is whatever is in the "Cells(i, lngColumnDate
).Value" - check it in your immediate window to see if it is a valid entry.

The "*" indicates that the date format will not switch orders (mm/dd vs.
dd/mm) depending on the operating system.



"Meltad" wrote:

Bill,

I'm still getting an error on this line
" datCheck = Format(Cells(i, 3).Value, "m/d/yyyy") "
I changed the 3 to lngColumnDate but still got an error on this line.
It must be to do with the "m/d/yyyy" part...

I have opened my CSV into Excel and all the dates are now in this format
"08/01/2006" (cell format is '*08/01/2001' - not sure what the asterisk
means!) this is dd/mm/yyy.

I'm still hopeful about using your code to solve this though!


"Bill Pfister" wrote:


Yes, the issue is that the code is looking in the wrong cell. I added a
variable "lngColumnDate" to hold the date column, so it is easier to modify.
See if this works better for you.

As for the CSV file, it depends on how the data looks after Excel reads it
(I'm assuming you're bring it in to Excel). It's a fairly subjective matter
that I'd most likely have to see in person to judge.


Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date
Dim lngColumnDate 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

lngColumnDate = 2

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
If (Format(Cells(i, lngColumnDate).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, lngColumnDate).EntireRow
Else
Set Source = Union(Source, Cells(i,
lngColumnDate).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub




"Meltad" wrote:

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I entered
"?Cells(i, 3).Value" - this is consignment number in column c but the date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to extract the
latest date data from and some of the records aren't formatted as date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and it will take you
to the visual basic editor. From there, press Ctrl-G to go to the Immediate
Window. In the Immediate Window, enter this line (without the quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing enter, there
should be a result below the line you typed/pasted - let me know what that
result is. I am curious to know whether the value contained in Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly do you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate window with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

This looks promising, the first part works fine now, its just the error on
this last line.
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
Once we crack this I'll be able to use this code for so much stuff, THANKS :-)


"Bill Pfister" wrote:

Make your For loop start at 2 instead of 1.


"Meltad" wrote:

Silly me!
Got it now...

?i
1
?lngColumnDate
2
?Cells(i, lngColumnDate ).Address
$B$1

So is this failing cos row 1 is the header and not a date? How do I set it
to look at row 2 and below?


"Bill Pfister" wrote:

The variables have no values because they are out of scope. You need to
check the variables while the program is running. Either set a breakpoint in
the code (inside of your "for...next" block, so it will get the "i" value) or
go into debug mode when it breaks.



"Meltad" wrote:

I put these 3 in the immediate window, ?i returns no value (just a blank
line), same with ?lngColumnDate (these used to have a value when hovered over
in code).
?Cells(i, lngColumnDate ).Address returns an error.


"Bill Pfister" wrote:

In the immediate window, checking these 3 items should help to indicate where
your problem lies:
?i
?lngColumnDate
?Cells(i, lngColumnDate ).Address



"Meltad" wrote:

You're right Bill,

This line in the immediate window returns a run time 1004 object or
application defined error....


"Bill Pfister" wrote:

The most likely culprit is whatever is in the "Cells(i, lngColumnDate
).Value" - check it in your immediate window to see if it is a valid entry.

The "*" indicates that the date format will not switch orders (mm/dd vs.
dd/mm) depending on the operating system.



"Meltad" wrote:

Bill,

I'm still getting an error on this line
" datCheck = Format(Cells(i, 3).Value, "m/d/yyyy") "
I changed the 3 to lngColumnDate but still got an error on this line.
It must be to do with the "m/d/yyyy" part...

I have opened my CSV into Excel and all the dates are now in this format
"08/01/2006" (cell format is '*08/01/2001' - not sure what the asterisk
means!) this is dd/mm/yyy.

I'm still hopeful about using your code to solve this though!


"Bill Pfister" wrote:


Yes, the issue is that the code is looking in the wrong cell. I added a
variable "lngColumnDate" to hold the date column, so it is easier to modify.
See if this works better for you.

As for the CSV file, it depends on how the data looks after Excel reads it
(I'm assuming you're bring it in to Excel). It's a fairly subjective matter
that I'd most likely have to see in person to judge.


Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date
Dim lngColumnDate 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

lngColumnDate = 2

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
If (Format(Cells(i, lngColumnDate).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, lngColumnDate).EntireRow
Else
Set Source = Union(Source, Cells(i,
lngColumnDate).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub




"Meltad" wrote:

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I entered
"?Cells(i, 3).Value" - this is consignment number in column c but the date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to extract the
latest date data from and some of the records aren't formatted as date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and it will take you
to the visual basic editor. From there, press Ctrl-G to go to the Immediate
Window. In the Immediate Window, enter this line (without the quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing enter, there
should be a result below the line you typed/pasted - let me know what that
result is. I am curious to know whether the value contained in Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly do you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate window with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

Look at the following variables for errors:

?Rows.Count
?Cells(Rows.Count, lngColumnDate).address
?Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).address


"Meltad" wrote:

This looks promising, the first part works fine now, its just the error on
this last line.
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
Once we crack this I'll be able to use this code for so much stuff, THANKS :-)


"Bill Pfister" wrote:

Make your For loop start at 2 instead of 1.


"Meltad" wrote:

Silly me!
Got it now...

?i
1
?lngColumnDate
2
?Cells(i, lngColumnDate ).Address
$B$1

So is this failing cos row 1 is the header and not a date? How do I set it
to look at row 2 and below?


"Bill Pfister" wrote:

The variables have no values because they are out of scope. You need to
check the variables while the program is running. Either set a breakpoint in
the code (inside of your "for...next" block, so it will get the "i" value) or
go into debug mode when it breaks.



"Meltad" wrote:

I put these 3 in the immediate window, ?i returns no value (just a blank
line), same with ?lngColumnDate (these used to have a value when hovered over
in code).
?Cells(i, lngColumnDate ).Address returns an error.


"Bill Pfister" wrote:

In the immediate window, checking these 3 items should help to indicate where
your problem lies:
?i
?lngColumnDate
?Cells(i, lngColumnDate ).Address



"Meltad" wrote:

You're right Bill,

This line in the immediate window returns a run time 1004 object or
application defined error....


"Bill Pfister" wrote:

The most likely culprit is whatever is in the "Cells(i, lngColumnDate
).Value" - check it in your immediate window to see if it is a valid entry.

The "*" indicates that the date format will not switch orders (mm/dd vs.
dd/mm) depending on the operating system.



"Meltad" wrote:

Bill,

I'm still getting an error on this line
" datCheck = Format(Cells(i, 3).Value, "m/d/yyyy") "
I changed the 3 to lngColumnDate but still got an error on this line.
It must be to do with the "m/d/yyyy" part...

I have opened my CSV into Excel and all the dates are now in this format
"08/01/2006" (cell format is '*08/01/2001' - not sure what the asterisk
means!) this is dd/mm/yyy.

I'm still hopeful about using your code to solve this though!


"Bill Pfister" wrote:


Yes, the issue is that the code is looking in the wrong cell. I added a
variable "lngColumnDate" to hold the date column, so it is easier to modify.
See if this works better for you.

As for the CSV file, it depends on how the data looks after Excel reads it
(I'm assuming you're bring it in to Excel). It's a fairly subjective matter
that I'd most likely have to see in person to judge.


Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date
Dim lngColumnDate 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

lngColumnDate = 2

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
If (Format(Cells(i, lngColumnDate).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, lngColumnDate).EntireRow
Else
Set Source = Union(Source, Cells(i,
lngColumnDate).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub




"Meltad" wrote:

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I entered
"?Cells(i, 3).Value" - this is consignment number in column c but the date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to extract the
latest date data from and some of the records aren't formatted as date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and it will take you
to the visual basic editor. From there, press Ctrl-G to go to the Immediate
Window. In the Immediate Window, enter this line (without the quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing enter, there
should be a result below the line you typed/pasted - let me know what that
result is. I am curious to know whether the value contained in Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly do you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate window with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"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

Hi Bill, I got...

?Rows.Count
65536
?Cells(Rows.Count, lngColumnDate).Address
$B$65536
?Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Address
ERROR

The xlUp value is -4162

So... the LF macro workbook is blank and I just need to paste the copied
rows into row 2 so I replaced the lngColumnDate in this line with a 3 and
this worked, all rows were pasted into the 2nd row of the blank workbook.
Does it matter that I've changed this?
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select

Thanks for helping me get to this point!
Mel



"Bill Pfister" wrote:

Look at the following variables for errors:

?Rows.Count
?Cells(Rows.Count, lngColumnDate).address
?Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).address


"Meltad" wrote:

This looks promising, the first part works fine now, its just the error on
this last line.
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
Once we crack this I'll be able to use this code for so much stuff, THANKS :-)


"Bill Pfister" wrote:

Make your For loop start at 2 instead of 1.


"Meltad" wrote:

Silly me!
Got it now...

?i
1
?lngColumnDate
2
?Cells(i, lngColumnDate ).Address
$B$1

So is this failing cos row 1 is the header and not a date? How do I set it
to look at row 2 and below?


"Bill Pfister" wrote:

The variables have no values because they are out of scope. You need to
check the variables while the program is running. Either set a breakpoint in
the code (inside of your "for...next" block, so it will get the "i" value) or
go into debug mode when it breaks.



"Meltad" wrote:

I put these 3 in the immediate window, ?i returns no value (just a blank
line), same with ?lngColumnDate (these used to have a value when hovered over
in code).
?Cells(i, lngColumnDate ).Address returns an error.


"Bill Pfister" wrote:

In the immediate window, checking these 3 items should help to indicate where
your problem lies:
?i
?lngColumnDate
?Cells(i, lngColumnDate ).Address



"Meltad" wrote:

You're right Bill,

This line in the immediate window returns a run time 1004 object or
application defined error....


"Bill Pfister" wrote:

The most likely culprit is whatever is in the "Cells(i, lngColumnDate
).Value" - check it in your immediate window to see if it is a valid entry.

The "*" indicates that the date format will not switch orders (mm/dd vs.
dd/mm) depending on the operating system.



"Meltad" wrote:

Bill,

I'm still getting an error on this line
" datCheck = Format(Cells(i, 3).Value, "m/d/yyyy") "
I changed the 3 to lngColumnDate but still got an error on this line.
It must be to do with the "m/d/yyyy" part...

I have opened my CSV into Excel and all the dates are now in this format
"08/01/2006" (cell format is '*08/01/2001' - not sure what the asterisk
means!) this is dd/mm/yyy.

I'm still hopeful about using your code to solve this though!


"Bill Pfister" wrote:


Yes, the issue is that the code is looking in the wrong cell. I added a
variable "lngColumnDate" to hold the date column, so it is easier to modify.
See if this works better for you.

As for the CSV file, it depends on how the data looks after Excel reads it
(I'm assuming you're bring it in to Excel). It's a fairly subjective matter
that I'd most likely have to see in person to judge.


Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date
Dim lngColumnDate 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

lngColumnDate = 2

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, lngColumnDate).Value) 0) Then
If (Format(Cells(i, lngColumnDate).Value, "m/d/yyyy") =
strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, lngColumnDate).EntireRow
Else
Set Source = Union(Source, Cells(i,
lngColumnDate).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, lngColumnDate).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub




"Meltad" wrote:

Thanks for the clarification Bill,

I've done that for you and it returned "CON Number" when I entered
"?Cells(i, 3).Value" - this is consignment number in column c but the date is
in column B.
Is this the reason for the error?

Also I've been looking at the CSV file I've been trying to extract the
latest date data from and some of the records aren't formatted as date - will
this also make a difference??

Cheers, Mel

"Bill Pfister" wrote:

When the code execution breaks, press the "Debug" button and it will take you
to the visual basic editor. From there, press Ctrl-G to go to the Immediate
Window. In the Immediate Window, enter this line (without the quotation
marks) "?Cells(i, 3).Value" and press Enter. After pressing enter, there
should be a result below the line you typed/pasted - let me know what that
result is. I am curious to know whether the value contained in Cells(i, 3)
is valid.



"Meltad" wrote:

Hi Bill,

Sorry I don't understand what you mean by this? What exactly do you want me
to do?

Mel


"Bill Pfister" wrote:

When you're on the line that breaks, go to the immediate window with this
line "?Cells(i, 3).Value" and let me know what it returns.


"Meltad" wrote:

Hi Bill,

I tried this but got an error quite early on (on this line)...
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

My date is in this format in the spreadsheet d/m/yy so I changed all your
"m/d/yyyy" to "d/m/yy" but this didn't solve it.


"Bill Pfister" wrote:

See if this does what you need.

Regards,
Bill



Sub LFmacro()

Dim nRows As Long
Dim i As Long
Dim Source As Range
Dim strCheckDate As String
Dim datCheck As Date
Dim datLatest As Date

'ChDir "G:\Lou French macro"
'Workbooks.Open Filename:="G:\Lou French macro\podnondel.CSV"

'copy latest date from podnondel workbook
'colour rows red

nRows = Cells.SpecialCells(xlCellTypeLastCell).Row

' Loop one time to find the latest date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
datCheck = Format(Cells(i, 3).Value, "m/d/yyyy")

If (datLatest < datCheck) Then datLatest = datCheck
End If
Next i

strCheckDate = Format(datLatest, "m/d/yyyy")

' Loop a second time to find all occurences of this date
For i = 1 To nRows
If (Len(Cells(i, 3).Value) 0) Then
If (Format(Cells(i, 3).Value, "m/d/yyyy") = strCheckDate) Then
If (Source Is Nothing) Then
Set Source = Cells(i, 3).EntireRow
Else
Set Source = Union(Source, Cells(i, 3).EntireRow)
End If
End If
End If
Next i

If (Source Is Nothing) Then
Call MsgBox("no dates found")
Exit Sub
End If

Source.Copy
Windows("LFmacro.XLS").Activate
Cells(Rows.Count, 3).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

End Sub


"Meltad" wrote:

Hi,
I'm trying to copy rows from one workbook to another - all those rows which
have the most recent date (date in column c).
Tom Ogilvy supplied this code for me but it just copies one row not all
instances of that date, any ideas???


"Tom Ogilvy" wrote:

Sub LFmacro()