I am working with 6 files. I need to copy the data from a sheet (within each
workbook/file) named 'substandard' and paste the information into the
'substandard' sheet in the master file. To do this I have been opening the
master file, placing the cursor where I want the information, opening the
file I wish to copy from, selecting the substandard sheet, copying the
information from the sheet and choosing paste special to the master file.
This takes forever. The files are all located in K:\fab\fab.xls,
K:\csb\csb.xls, K:\bsf\bsf.xls, K:\bag\bag.xls K:\bnp\bnp.xls to be
pasted into K:\corp\corp.xls. The sheets to select from within the files is
named 'substandard' - all with the same headings. I need to copy starting
from cell A6.
The sheets all have different amounts of data so I can't set a static range
to copy. And I don't know where it will need to be pasted in the master
sheet, or in other words the next empty row.

I know how to record a macro with the macro writer, but that is about it and
since the amount of data varies I am stumped.

Any suggestions??
Thanks for any help.
Marie

You can start here Marie
http://www.rondebruin.nl/copy3.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Marie" wrote in message ...
I am working with 6 files. I need to copy the data from a sheet (within each
workbook/file) named 'substandard' and paste the information into the
'substandard' sheet in the master file. To do this I have been opening the
master file, placing the cursor where I want the information, opening the
file I wish to copy from, selecting the substandard sheet, copying the
information from the sheet and choosing paste special to the master file.
This takes forever. The files are all located in K:\fab\fab.xls,
K:\csb\csb.xls, K:\bsf\bsf.xls, K:\bag\bag.xls K:\bnp\bnp.xls to be
pasted into K:\corp\corp.xls. The sheets to select from within the files is
named 'substandard' - all with the same headings. I need to copy starting
from cell A6.
The sheets all have different amounts of data so I can't set a static range
to copy. And I don't know where it will need to be pasted in the master
sheet, or in other words the next empty row.

I know how to record a macro with the macro writer, but that is about it and
since the amount of data varies I am stumped.

Any suggestions??
Thanks for any help.
Marie

Thanks! The site is very helpful!

I think the code I need is 'Copy all cells from the sheet or all cells
without the header row'

As I said I know some basic VB. I have a couple of questions about the code.

MyPath = "C:\Data"

I assume this is where I place the name of the folder I want to pull the
data from. Do all the files have to be in the same folder? In the example
above are all the files being copied in 'Data'. As I said mine are in
K:\csb\csb.xls and K:\bag\bag.xls, etc. Six different folders. Do I need to
create a folder to save them all in for this to work? They all have a sheet
that I want to copy with the same name but the files are all in different
folders.

And this line 'Copy from A2:IV? (till the last row with data on your sheet)

Is the IV like a wildcard? Does it copy everything on the sheet?

Thanks for your patience. I really appreciate it!

Marie




"Ron de Bruin" wrote:

You can start here Marie
http://www.rondebruin.nl/copy3.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Marie" wrote in message ...
I am working with 6 files. I need to copy the data from a sheet (within each
workbook/file) named 'substandard' and paste the information into the
'substandard' sheet in the master file. To do this I have been opening the
master file, placing the cursor where I want the information, opening the
file I wish to copy from, selecting the substandard sheet, copying the
information from the sheet and choosing paste special to the master file.
This takes forever. The files are all located in K:\fab\fab.xls,
K:\csb\csb.xls, K:\bsf\bsf.xls, K:\bag\bag.xls K:\bnp\bnp.xls to be
pasted into K:\corp\corp.xls. The sheets to select from within the files is
named 'substandard' - all with the same headings. I need to copy starting
from cell A6.
The sheets all have different amounts of data so I can't set a static range
to copy. And I don't know where it will need to be pasted in the master
sheet, or in other words the next empty row.

I know how to record a macro with the macro writer, but that is about it and
since the amount of data varies I am stumped.

Any suggestions??
Thanks for any help.
Marie