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

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.

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" <[email protected]> wrote in message
news:MFkog.104034$IK3.70468@pd7tw1no...
> 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.
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:MFkog.104034$IK3.70468@pd7tw1no...
>> 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.
>>
>>
>
>

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" <[email protected]> wrote in message
news:nlmpg.112067$Mn5.105670@pd7tw3no...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:MFkog.104034$IK3.70468@pd7tw1no...
> >> 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.
> >>
> >>
> >
> >
>
>

Will do.

Thanks.



"Bob Phillips" <[email protected]> wrote in message
news:O6%[email protected]...
> 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" <[email protected]> wrote in message
> news:nlmpg.112067$Mn5.105670@pd7tw3no...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:MFkog.104034$IK3.70468@pd7tw1no...
>> >> 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.
>> >>
>> >>
>> >
>> >
>>
>>
>
>

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.







"Bob Phillips" <[email protected]> wrote in message
news:O6%[email protected]...
> 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" <[email protected]> wrote in message
> news:nlmpg.112067$Mn5.105670@pd7tw3no...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:MFkog.104034$IK3.70468@pd7tw1no...
>> >> 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.
>> >>
>> >>
>> >
>> >
>>
>>
>
>