Macro copying info/data in multiple excel files into one summary file.
 

Could someone please help me with an excel macro to copy data/information
from multiple Excel files (each info is in the same cell and same column
label in each file) into one Excel file , each source file info going to one
row in the destination file.

--
I travel 3rd Class because there is no 4th Class- Gandhi.

Macro copying info/data in multiple excel files into one summary file.
 

Hi Jskasango

Try this
http://www.rondebruin.nl/copy3.htm


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


"Jskasango" <u20293@uwe wrote in message news:5e0775d979bc0@uwe...
Could someone please help me with an excel macro to copy data/information
from multiple Excel files (each info is in the same cell and same column
label in each file) into one Excel file , each source file info going to one
row in the destination file.

--
I travel 3rd Class because there is no 4th Class- Gandhi.

Macro copying info/data in multiple excel files into one summary file.
 

Thanks Ron,
I am not an expert but let me study your suggestions and I will let you know
my progress.
I am asuming that I will cut and paste, and change your examples of cell refs
and filenames to suit mine.
Kasango.

Ron de Bruin wrote:
Hi Jskasango

Try this
http://www.rondebruin.nl/copy3.htm

Could someone please help me with an excel macro to copy data/information
from multiple Excel files (each info is in the same cell and same column
label in each file) into one Excel file , each source file info going to one
row in the destination file.

--
I travel 3rd Class because there is no 4th Class- Gandhi.

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1

Macro copying info/data in multiple excel files into one summary file.
 

Yes change the folder name and range and try it

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


"Jskasango via OfficeKB.com" <u20293@uwe wrote in message news:5e13e3e2a80e9@uwe...
Thanks Ron,
I am not an expert but let me study your suggestions and I will let you know
my progress.
I am asuming that I will cut and paste, and change your examples of cell refs
and filenames to suit mine.
Kasango.

Ron de Bruin wrote:
Hi Jskasango

Try this
http://www.rondebruin.nl/copy3.htm

Could someone please help me with an excel macro to copy data/information
from multiple Excel files (each info is in the same cell and same column
label in each file) into one Excel file , each source file info going to one
row in the destination file.

--
I travel 3rd Class because there is no 4th Class- Gandhi.

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1

Macro copying info/data in multiple excel files into one summary file.
 

I am experimenting with Sub Example2(), my worksheets both in the source and
destination have names. Where in the code do I specify these names? I need
to pick information from various cells.

Ron de Bruin wrote:
Yes change the folder name and range and try it

Thanks Ron,
I am not an expert but let me study your suggestions and I will let you know
[quoted text clipped - 12 lines]
label in each file) into one Excel file , each source file info going to one
row in the destination file.

--
I travel 3rd Class because there is no 4th Class- Gandhi.

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200604/1

Macro copying info/data in multiple excel files into one summary file.
 

It is working now, but I need to pick up more than 50 different pieces of
information from each spreadsheet and take it to the basefile! Is there a way
that I can specify one by one cell reference in the code and specify the
destination cell ref as well?

Jskasango wrote:
I am experimenting with Sub Example2(), my worksheets both in the source and
destination have names. Where in the code do I specify these names? I need
to pick information from various cells.

Yes change the folder name and range and try it

[quoted text clipped - 3 lines]
label in each file) into one Excel file , each source file info going to one
row in the destination file.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200604/1

Macro copying info/data in multiple excel files into one summary file.
 

Maybe ?
http://www.rondebruin.nl/copy3.htm#separate


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


"Jskasango via OfficeKB.com" <u20293@uwe wrote in message news:5e376a4965bdd@uwe...
It is working now, but I need to pick up more than 50 different pieces of
information from each spreadsheet and take it to the basefile! Is there a way
that I can specify one by one cell reference in the code and specify the
destination cell ref as well?

Jskasango wrote:
I am experimenting with Sub Example2(), my worksheets both in the source and
destination have names. Where in the code do I specify these names? I need
to pick information from various cells.

Yes change the folder name and range and try it

[quoted text clipped - 3 lines]
label in each file) into one Excel file , each source file info going to one
row in the destination file.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200604/1

Macro copying info/data in multiple excel files into one summary file.
 

I have found Sub FSO_Example_1() (copy from excel files in folders and sub-
folders) extremely useful! BUT, now my problem is how to pick contents or
substrings of contents of many different sourceRanges, and how to indicate
the different destination ranges which are not necessarily next to each other,
although for each file they are to be put on the same row.
I am on my knees in prayer hoping that this is the last hustle, once I can
get this, I will be able to fly.

Regards,
Kasango.




Ron de Bruin wrote:
Maybe ?
http://www.rondebruin.nl/copy3.htm#separate

It is working now, but I need to pick up more than 50 different pieces of
information from each spreadsheet and take it to the basefile! Is there a way
[quoted text clipped - 10 lines]
label in each file) into one Excel file , each source file info going to one
row in the destination file.

--
I travel 3rd Class because there is no 4th Class- Gandhi.

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200604/1

Macro copying info/data in multiple excel files into one summary file.
 

Can you give a example what you want

which are not necessarily next to each other,
The last link I posted is doing that


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


"Jskasango via OfficeKB.com" <u20293@uwe wrote in message news:5e453e3fac3a9@uwe...
I have found Sub FSO_Example_1() (copy from excel files in folders and sub-
folders) extremely useful! BUT, now my problem is how to pick contents or
substrings of contents of many different sourceRanges, and how to indicate
the different destination ranges which are not necessarily next to each other,
although for each file they are to be put on the same row.
I am on my knees in prayer hoping that this is the last hustle, once I can
get this, I will be able to fly.

Regards,
Kasango.




Ron de Bruin wrote:
Maybe ?
http://www.rondebruin.nl/copy3.htm#separate

It is working now, but I need to pick up more than 50 different pieces of
information from each spreadsheet and take it to the basefile! Is there a way
[quoted text clipped - 10 lines]
label in each file) into one Excel file , each source file info going to one
row in the destination file.

--
I travel 3rd Class because there is no 4th Class- Gandhi.

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200604/1

Macro copying info/data in multiple excel files into one summary file.
 

Hi Ron,

You are right. It is doing it. I will paste below a sample of how I have
achieved it but I am sure there must be a more professional and smooth way of
doing it. Thanks a million. My objective today is to get into cell in the
source file and copy only part of the contents.

This is how I modified the Sub FSO_Example_1():-

Set sourceRange = mybook.Worksheets(1).Range("A1:A1")
Set sourceRange2 = mybook.Worksheets(1).Range("H1:H1")
Set sourceRange3 = mybook.Worksheets(1).Range("V1:V1")

SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Range("AB" & rnum)
Set destrange2 = basebook.Worksheets(1).Range("F" & rnum)
Set destrange3 = basebook.Worksheets(1).Range("AD" & rnum)

I have just noticed that although it works, I forgot to mention Sourcerange3
at the top of the code, but it still worked!
Am I on the correct path? By tomoroww I want to be able to avoid duplicates
but I dont know how to do it.

Please have a very pleasant day.




Ron de Bruin wrote:
Can you give a example what you want

which are not necessarily next to each other,
The last link I posted is doing that

I have found Sub FSO_Example_1() (copy from excel files in folders and sub-
folders) extremely useful! BUT, now my problem is how to pick contents or
[quoted text clipped - 15 lines]
label in each file) into one Excel file , each source file info going to one
row in the destination file.

--
I travel 3rd Class because there is no 4th Class- Gandhi.

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200604/1

Macro copying info/data in multiple excel files into one summary file.
 

Try this

Do While FNames < ""
Set mybook = Workbooks.Open(FNames)

basebook.Worksheets(1).Range("AB" & rnum).Value = mybook.Worksheets(1).Range("A1").Value
basebook.Worksheets(1).Range("F" & rnum).Value = mybook.Worksheets(1).Range("H1").Value
basebook.Worksheets(1).Range("AD" & rnum).Value = mybook.Worksheets(1).Range("V1").Value


mybook.Close False
rnum = rnum + 1
FNames = Dir()
Loop

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


"Jskasango via OfficeKB.com" <u20293@uwe wrote in message news:5e4de9fa4620a@uwe...
Hi Ron,

You are right. It is doing it. I will paste below a sample of how I have
achieved it but I am sure there must be a more professional and smooth way of
doing it. Thanks a million. My objective today is to get into cell in the
source file and copy only part of the contents.

This is how I modified the Sub FSO_Example_1():-

Set sourceRange = mybook.Worksheets(1).Range("A1:A1")
Set sourceRange2 = mybook.Worksheets(1).Range("H1:H1")
Set sourceRange3 = mybook.Worksheets(1).Range("V1:V1")

SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Range("AB" & rnum)
Set destrange2 = basebook.Worksheets(1).Range("F" & rnum)
Set destrange3 = basebook.Worksheets(1).Range("AD" & rnum)

I have just noticed that although it works, I forgot to mention Sourcerange3
at the top of the code, but it still worked!
Am I on the correct path? By tomoroww I want to be able to avoid duplicates
but I dont know how to do it.

Please have a very pleasant day.




Ron de Bruin wrote:
Can you give a example what you want

which are not necessarily next to each other,
The last link I posted is doing that

I have found Sub FSO_Example_1() (copy from excel files in folders and sub-
folders) extremely useful! BUT, now my problem is how to pick contents or
[quoted text clipped - 15 lines]
label in each file) into one Excel file , each source file info going to one
row in the destination file.

--
I travel 3rd Class because there is no 4th Class- Gandhi.

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200604/1