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

[Jskasango]

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.

 

[Ron de Bruin]

Hi Jskasango

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

 

[Jskasango via OfficeKB.com]

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]

Yes change the folder name and range and try it

 

[Jskasango via OfficeKB.com]

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

Thanks Ron,
I am not an expert but let me study your suggestions and I will let you know

[quoted text clipped - 12 lines]

 

[Jskasango via OfficeKB.com]

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?

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]

 

[Ron de Bruin]

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


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

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?

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.

 

[Jskasango via OfficeKB.com]

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.

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]

 

[Ron de Bruin]

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

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.

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.aspx/excel-programming/200604/1

 

[Jskasango via OfficeKB.com]

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.

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]

 

[Ron de Bruin]

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

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.

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.aspx/excel-programming/200604/1