Hey everyone. So I have an Excell workbook where the user keeps track of
his/her sales data, basically to have record of which accounts were accessed,
how many sales were made, etc.

Now before you ask, the company doesn't want Access :/

What I need to do is copy the information they put in (from A2:L2, all the
way to A21:L21, for each sheet (there are 14 sheets), and put it on a final
sheet which would be used for exporting purposes. Now we also need the
information to NOT copy if it's a blank row, and update on-the-fly. Is there
a method of doing this in Excell 2003/2007?

Thanks in advance, for any help.

Chris

Something like (un tested)

sub copytomaster()
for each ws in worksheets
if ws.name <>"Master" then
dlr=sheets("Master").cells(rows.count,"a").end(xlup).row+1
ws.range("a2:l21").copy sheets("Master").cells(dlr,"a")
next ws
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Chris" <(E-Mail Removed)> wrote in message
news:9693987E-90C9-4B28-97D6-(E-Mail Removed)...
> Hey everyone. So I have an Excell workbook where the user keeps track of
> his/her sales data, basically to have record of which accounts were
> accessed,
> how many sales were made, etc.
>
> Now before you ask, the company doesn't want Access :/
>
> What I need to do is copy the information they put in (from A2:L2, all the
> way to A21:L21, for each sheet (there are 14 sheets), and put it on a
> final
> sheet which would be used for exporting purposes. Now we also need the
> information to NOT copy if it's a blank row, and update on-the-fly. Is
> there
> a method of doing this in Excell 2003/2007?
>
> Thanks in advance, for any help.
>
> Chris

Thanks, Don. There's a bit more to this that I forgot to include.

The first page is a summary of all 14 days' worth of information, then
there's a script to change the names of each worksheet to the correct date
(so right now, it would go from 5-9, 5-10, all the way to 5-22). Then
finally would be the backup sheet. Also, would what you've posted below
actually check (and skip) the empty lines?

Thanks again,

Chris

"Don Guillett" wrote:

> Something like (un tested)
>
> sub copytomaster()
> for each ws in worksheets
> if ws.name <>"Master" then
> dlr=sheets("Master").cells(rows.count,"a").end(xlup).row+1
> ws.range("a2:l21").copy sheets("Master").cells(dlr,"a")
> next ws
> end sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Chris" <(E-Mail Removed)> wrote in message
> news:9693987E-90C9-4B28-97D6-(E-Mail Removed)...
> > Hey everyone. So I have an Excell workbook where the user keeps track of
> > his/her sales data, basically to have record of which accounts were
> > accessed,
> > how many sales were made, etc.
> >
> > Now before you ask, the company doesn't want Access :/
> >
> > What I need to do is copy the information they put in (from A2:L2, all the
> > way to A21:L21, for each sheet (there are 14 sheets), and put it on a
> > final
> > sheet which would be used for exporting purposes. Now we also need the
> > information to NOT copy if it's a blank row, and update on-the-fly. Is
> > there
> > a method of doing this in Excell 2003/2007?
> >
> > Thanks in advance, for any help.
> >
> > Chris
>
>

Easier if you just send your file to my address below along with a clear
explanation, copy of this msg and before/after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Chris" <(E-Mail Removed)> wrote in message
news:568E5EDE-A0DA-49E5-A513-(E-Mail Removed)...
> Thanks, Don. There's a bit more to this that I forgot to include.
>
> The first page is a summary of all 14 days' worth of information, then
> there's a script to change the names of each worksheet to the correct date
> (so right now, it would go from 5-9, 5-10, all the way to 5-22). Then
> finally would be the backup sheet. Also, would what you've posted below
> actually check (and skip) the empty lines?
>
> Thanks again,
>
> Chris
>
> "Don Guillett" wrote:
>
>> Something like (un tested)
>>
>> sub copytomaster()
>> for each ws in worksheets
>> if ws.name <>"Master" then
>> dlr=sheets("Master").cells(rows.count,"a").end(xlup).row+1
>> ws.range("a2:l21").copy sheets("Master").cells(dlr,"a")
>> next ws
>> end sub
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Chris" <(E-Mail Removed)> wrote in message
>> news:9693987E-90C9-4B28-97D6-(E-Mail Removed)...
>> > Hey everyone. So I have an Excell workbook where the user keeps track
>> > of
>> > his/her sales data, basically to have record of which accounts were
>> > accessed,
>> > how many sales were made, etc.
>> >
>> > Now before you ask, the company doesn't want Access :/
>> >
>> > What I need to do is copy the information they put in (from A2:L2, all
>> > the
>> > way to A21:L21, for each sheet (there are 14 sheets), and put it on a
>> > final
>> > sheet which would be used for exporting purposes. Now we also need the
>> > information to NOT copy if it's a blank row, and update on-the-fly. Is
>> > there
>> > a method of doing this in Excell 2003/2007?
>> >
>> > Thanks in advance, for any help.
>> >
>> > Chris
>>
>>