Macro to run daily and delete blank lines as well as duplicate entries.

[Art MacNeil]

Hi all,

I get a new a spreadsheet every day. It has the month in the filename.
This month's spreadsheet is called "June data".

In that spreadsheet, there are tabs for each day of the month. The first
tab is called 1, the second is called 2 and so on.

The source spreadsheet ("June data") and the first tab ("1") has more data
than I need. I just need to copy data in 4 columns to a destination
spreadsheet. At the moment this is called Revenue Tracker. The Macro is
saved in Revenue Tracker.

I have already written a Macro to do this, but this is where I'm stuck.

My Macro will copy the data in the 4 columns from Line 2 through Line 125
and paste that data to a destination spreadsheet ("Revenue Tracker").

The data has lots of blank lines as well as duplicate entries. I want to
remove the blank lines as well as any duplicate entries.

The source file is made from a template so it always ends at Line 125 but
can have any numbers of entries. So the tabs numbered 1 through 30 all end
at Line 125.


The questions:

How do I remove those blank lines?

How do I delete the duplicate entries?

Can I use Loop (or a similar function) to run these steps for each day,
rather than write code for each day of the month? (i.e. Day 1 would use up
lines 2 through 126, and day 2 would use lines 127 through 252, etc).

Today is the 27th. If I only need to run the Macro for any days that
haven't been copied so far. I don't really need to start at Day 1 every
time I run the Macro.



Your help would be greatly appreciated,

Art.

 

[Bob Phillips]

This will tidy up the source before you do your stuff


Set oWb = Workbooks("June data".xls")

For Each sh In oWb.Worksheets
iLastRow = sh.Cells(sh.Rows.Count,"A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If sh.Cells(i,"A").Value = "" Then sh.Rows(i).Delete
If Application.Countif(sh.Cells(i,"A").Value, sh.Columns(1),0) >
1 Then
sh.Rows.delete
End If
Next i
Next sh

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Art MacNeil]

Thank you for replying Bob,

I tried the code below and these 2 lines won't run.

If Application.Countif(sh.Cells(i,"A").Value, sh.Columns(1),0) >
1 Then


Any ideas?

Thanks,

Art.

Sorry for the delay, I was away for a few days.

 

[Bob Phillips]

That should be one line. The NG has wrapped the code around, so move the 1
Then to the end of the previous line.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Art MacNeil]

Will do.

Thanks.

That should be one line. The NG has wrapped the code around, so move the 1
Then to the end of the previous line.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Art MacNeil]

I tried it and I'm getting a compile error. "End if without block if"

The code now looks like this: (I'll keep it short so the ng doesn't wrap
it)

Set oWb = Workbooks...

For Each sh In o...
iLastRow = sh.Ce...
For i = iLastRow To...
If sh.Cells(i, "A")....
If Application.Count...
End If

Next i
Next sh

End Sub



Thank you for your help,

Art.