Copy certain cells to a "backup" sheet using VB
 

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

Copy certain cells to a "backup" sheet using VB
 

Something like (un tested)

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

Copy certain cells to a "backup" sheet using VB
 

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(xlu p).row+1
ws.range("a2:l21").copy sheets("Master").cells(dlr,"a")
next ws
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

Copy certain cells to a "backup" sheet using VB
 

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

"Chris" wrote in message
...
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(xlu p).row+1
ws.range("a2:l21").copy sheets("Master").cells(dlr,"a")
next ws
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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