Heres my goal. I have a directory full of .xls files, around 500 or
so. I would like to
merge all these files (all located in c:\data) into one master file.
Doing
this through a macro would be prefered because importing each file
individually would take about 2 months to complete :)

All XLS files are the same. The formating in all 500 xls files are
identical. The only thing that varies is the data held with-in. The
columns and what not are the same. They just hold information for
specific dates. And I want to make one master that combines all the
information from the past 2 years into one XLS file.

Any help with this? Im new to macro's and dont really know where to
start :(

Hi wmureports

Start here
http://www.rondebruin.nl/copy3.htm

Post back if you need more help


--
Regards Ron De Bruin
http://www.rondebruin.nl



"wmureports" wrote in message oups.com...
Heres my goal. I have a directory full of .xls files, around 500 or
so. I would like to
merge all these files (all located in c:\data) into one master file.
Doing
this through a macro would be prefered because importing each file
individually would take about 2 months to complete :)

All XLS files are the same. The formating in all 500 xls files are
identical. The only thing that varies is the data held with-in. The
columns and what not are the same. They just hold information for
specific dates. And I want to make one master that combines all the
information from the past 2 years into one XLS file.

Any help with this? Im new to macro's and dont really know where to
start :(

Thanks Ron, Ill check it out and let you know how it goes.


Ron de Bruin wrote:
Hi wmureports

Start here
http://www.rondebruin.nl/copy3.htm

Post back if you need more help


--
Regards Ron De Bruin
http://www.rondebruin.nl



"wmureports" wrote in message oups.com...
Heres my goal. I have a directory full of .xls files, around 500 or
so. I would like to
merge all these files (all located in c:\data) into one master file.
Doing
this through a macro would be prefered because importing each file
individually would take about 2 months to complete :)

All XLS files are the same. The formating in all 500 xls files are
identical. The only thing that varies is the data held with-in. The
columns and what not are the same. They just hold information for
specific dates. And I want to make one master that combines all the
information from the past 2 years into one XLS file.

Any help with this? Im new to macro's and dont really know where to
start :(

Ok question for you... Ive been reading through your tutorial and been
playing around implementing it into my master record. Few questions.
The header on each page goes from Cell A1:F1, A2:F2, & A3:F3. So the
actual data doesnt start till A4:F4. So how do i set it to only start
copying the information from A4:F4 and beyond all the way down to the
bottom of the page wether it be A100:F100 or A500:F500.

Then it needs to start inserting the data into field A4 on the master.

Your help is appreciated.

wmureports wrote:
Thanks Ron, Ill check it out and let you know how it goes.


Ron de Bruin wrote:
Hi wmureports

Start here
http://www.rondebruin.nl/copy3.htm

Post back if you need more help


--
Regards Ron De Bruin
http://www.rondebruin.nl



"wmureports" wrote in message oups.com...
Heres my goal. I have a directory full of .xls files, around 500 or
so. I would like to
merge all these files (all located in c:\data) into one master file.
Doing
this through a macro would be prefered because importing each file
individually would take about 2 months to complete :)

All XLS files are the same. The formating in all 500 xls files are
identical. The only thing that varies is the data held with-in. The
columns and what not are the same. They just hold information for
specific dates. And I want to make one master that combines all the
information from the past 2 years into one XLS file.

Any help with this? Im new to macro's and dont really know where to
start :(

Hi wmureports

See
http://www.rondebruin.nl/copy3.htm#header

Change

rnum = 1
to
rnum = 4

And

Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow)
to
Set sourceRange = mybook.Worksheets(1).Range("A4:IV" & lrow)




--
Regards Ron De Bruin
http://www.rondebruin.nl



"wmureports" wrote in message oups.com...
Ok question for you... Ive been reading through your tutorial and been
playing around implementing it into my master record. Few questions.
The header on each page goes from Cell A1:F1, A2:F2, & A3:F3. So the
actual data doesnt start till A4:F4. So how do i set it to only start
copying the information from A4:F4 and beyond all the way down to the
bottom of the page wether it be A100:F100 or A500:F500.

Then it needs to start inserting the data into field A4 on the master.

Your help is appreciated.

wmureports wrote:
Thanks Ron, Ill check it out and let you know how it goes.


Ron de Bruin wrote:
Hi wmureports

Start here
http://www.rondebruin.nl/copy3.htm

Post back if you need more help


--
Regards Ron De Bruin
http://www.rondebruin.nl



"wmureports" wrote in message oups.com...
Heres my goal. I have a directory full of .xls files, around 500 or
so. I would like to
merge all these files (all located in c:\data) into one master file.
Doing
this through a macro would be prefered because importing each file
individually would take about 2 months to complete :)

All XLS files are the same. The formating in all 500 xls files are
identical. The only thing that varies is the data held with-in. The
columns and what not are the same. They just hold information for
specific dates. And I want to make one master that combines all the
information from the past 2 years into one XLS file.

Any help with this? Im new to macro's and dont really know where to
start :(

Ok one more question... It seems to be copying certain fields that
have no data.. What if statement would i use to not add blank records?

If basebook.Worksheets(1).Cells.Value < "" or something along those
lines??

Thanks,


Ron de Bruin wrote:
Hi wmureports

See
http://www.rondebruin.nl/copy3.htm#header

Change

rnum = 1
to
rnum = 4

And

Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow)
to
Set sourceRange = mybook.Worksheets(1).Range("A4:IV" & lrow)




--
Regards Ron De Bruin
http://www.rondebruin.nl



"wmureports" wrote in message oups.com...
Ok question for you... Ive been reading through your tutorial and been
playing around implementing it into my master record. Few questions.
The header on each page goes from Cell A1:F1, A2:F2, & A3:F3. So the
actual data doesnt start till A4:F4. So how do i set it to only start
copying the information from A4:F4 and beyond all the way down to the
bottom of the page wether it be A100:F100 or A500:F500.

Then it needs to start inserting the data into field A4 on the master.

Your help is appreciated.

wmureports wrote:
Thanks Ron, Ill check it out and let you know how it goes.


Ron de Bruin wrote:
Hi wmureports

Start here
http://www.rondebruin.nl/copy3.htm

Post back if you need more help


--
Regards Ron De Bruin
http://www.rondebruin.nl



"wmureports" wrote in message oups.com...
Heres my goal. I have a directory full of .xls files, around 500 or
so. I would like to
merge all these files (all located in c:\data) into one master file.
Doing
this through a macro would be prefered because importing each file
individually would take about 2 months to complete :)

All XLS files are the same. The formating in all 500 xls files are
identical. The only thing that varies is the data held with-in. The
columns and what not are the same. They just hold information for
specific dates. And I want to make one master that combines all the
information from the past 2 years into one XLS file.

Any help with this? Im new to macro's and dont really know where to
start :(

Actually it should only ignore it if the whole row of data is blank.
Some rows have data mixed in with blank fields which is fine, but Id
like the macro to completely ignore complete rows that contain no data.
Thanks


wmureports wrote:
Ok one more question... It seems to be copying certain fields that
have no data.. What if statement would i use to not add blank records?

If basebook.Worksheets(1).Cells.Value < "" or something along those
lines??

Thanks,


Ron de Bruin wrote:
Hi wmureports

See
http://www.rondebruin.nl/copy3.htm#header

Change

rnum = 1
to
rnum = 4

And

Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow)
to
Set sourceRange = mybook.Worksheets(1).Range("A4:IV" & lrow)




--
Regards Ron De Bruin
http://www.rondebruin.nl



"wmureports" wrote in message oups.com...
Ok question for you... Ive been reading through your tutorial and been
playing around implementing it into my master record. Few questions.
The header on each page goes from Cell A1:F1, A2:F2, & A3:F3. So the
actual data doesnt start till A4:F4. So how do i set it to only start
copying the information from A4:F4 and beyond all the way down to the
bottom of the page wether it be A100:F100 or A500:F500.

Then it needs to start inserting the data into field A4 on the master.

Your help is appreciated.

wmureports wrote:
Thanks Ron, Ill check it out and let you know how it goes.


Ron de Bruin wrote:
Hi wmureports

Start here
http://www.rondebruin.nl/copy3.htm

Post back if you need more help


--
Regards Ron De Bruin
http://www.rondebruin.nl



"wmureports" wrote in message oups.com...
Heres my goal. I have a directory full of .xls files, around 500 or
so. I would like to
merge all these files (all located in c:\data) into one master file.
Doing
this through a macro would be prefered because importing each file
individually would take about 2 months to complete :)

All XLS files are the same. The formating in all 500 xls files are
identical. The only thing that varies is the data held with-in. The
columns and what not are the same. They just hold information for
specific dates. And I want to make one master that combines all the
information from the past 2 years into one XLS file.

Any help with this? Im new to macro's and dont really know where to
start :(

Hi wmureports

If you want to check the whole row then you must loop through the whole list or
add one column with a formula in the sheet with the merged data and AutoFilter on this column

Use the countA worksheetfunction for this in a column to the right of your last column with data
use a If function that display "Delete" if the count = 0
you can Autofilter on that column then and delete the visible rows



--
Regards Ron De Bruin
http://www.rondebruin.nl



"wmureports" wrote in message ps.com...
Actually it should only ignore it if the whole row of data is blank.
Some rows have data mixed in with blank fields which is fine, but Id
like the macro to completely ignore complete rows that contain no data.
Thanks


wmureports wrote:
Ok one more question... It seems to be copying certain fields that
have no data.. What if statement would i use to not add blank records?

If basebook.Worksheets(1).Cells.Value < "" or something along those
lines??

Thanks,


Ron de Bruin wrote:
Hi wmureports

See
http://www.rondebruin.nl/copy3.htm#header

Change

rnum = 1
to
rnum = 4

And

Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow)
to
Set sourceRange = mybook.Worksheets(1).Range("A4:IV" & lrow)




--
Regards Ron De Bruin
http://www.rondebruin.nl



"wmureports" wrote in message oups.com...
Ok question for you... Ive been reading through your tutorial and been
playing around implementing it into my master record. Few questions.
The header on each page goes from Cell A1:F1, A2:F2, & A3:F3. So the
actual data doesnt start till A4:F4. So how do i set it to only start
copying the information from A4:F4 and beyond all the way down to the
bottom of the page wether it be A100:F100 or A500:F500.

Then it needs to start inserting the data into field A4 on the master.

Your help is appreciated.

wmureports wrote:
Thanks Ron, Ill check it out and let you know how it goes.


Ron de Bruin wrote:
Hi wmureports

Start here
http://www.rondebruin.nl/copy3.htm

Post back if you need more help


--
Regards Ron De Bruin
http://www.rondebruin.nl



"wmureports" wrote in message oups.com...
Heres my goal. I have a directory full of .xls files, around 500 or
so. I would like to
merge all these files (all located in c:\data) into one master file.
Doing
this through a macro would be prefered because importing each file
individually would take about 2 months to complete :)

All XLS files are the same. The formating in all 500 xls files are
identical. The only thing that varies is the data held with-in. The
columns and what not are the same. They just hold information for
specific dates. And I want to make one master that combines all the
information from the past 2 years into one XLS file.

Any help with this? Im new to macro's and dont really know where to
start :(