Compiling data from a particular location in similar workboooks
 

I have a large number of workbooks with identical structures. I want to get
information from a location on a particular sheet in every one of these
workbooks, and list it in a new workbook. Any ideas of how to do this
without having to copy-and-paste from each of the locations or manually enter
the source for each of these books?

Thanks!

Margureite;
You can link the workbooks. Just go to the new workbook and click "=" then
go to the spot in the other workbook and click on the cell you want to
transfer over, then press enter. Whenever you change it in the original it
will update in the destination.

"Marguerite" wrote:

I have a large number of workbooks with identical structures. I want to get
information from a location on a particular sheet in every one of these
workbooks, and list it in a new workbook. Any ideas of how to do this
without having to copy-and-paste from each of the locations or manually enter
the source for each of these books?

Thanks!

Marguerite,

You could use INDIRECT, but you'd still need to type in the file names that
you wanted.

Eric


"Marguerite" wrote:

I have a large number of workbooks with identical structures. I want to get
information from a location on a particular sheet in every one of these
workbooks, and list it in a new workbook. Any ideas of how to do this
without having to copy-and-paste from each of the locations or manually enter
the source for each of these books?

Thanks!

I was hoping not to have to open and select within each individual file,
since there are so many... got any other ideas?

"Bartman" wrote:

Margureite;
You can link the workbooks. Just go to the new workbook and click "=" then
go to the spot in the other workbook and click on the cell you want to
transfer over, then press enter. Whenever you change it in the original it
will update in the destination.

"Marguerite" wrote:

I have a large number of workbooks with identical structures. I want to get
information from a location on a particular sheet in every one of these
workbooks, and list it in a new workbook. Any ideas of how to do this
without having to copy-and-paste from each of the locations or manually enter
the source for each of these books?

Thanks!

This doesn't eliminate the problem of needing to access/type in all those
different workbook files... :(

"Eric" wrote:

Marguerite,

You could use INDIRECT, but you'd still need to type in the file names that
you wanted.

Eric


"Marguerite" wrote:

I have a large number of workbooks with identical structures. I want to get
information from a location on a particular sheet in every one of these
workbooks, and list it in a new workbook. Any ideas of how to do this
without having to copy-and-paste from each of the locations or manually enter
the source for each of these books?

Thanks!

Open all workbooks and use this to reference cell Sheet1!$C$1 in all
workbooks in column A of the active sheet:

Sub RefWBs()
c = 0
For Each wb In Application.Workbooks
c = c + 1
ref = "=[" & wb.Name & "]Sheet1!$C$1"
Range("A" & c) = ref
Range("B" & c) = wb.Name 'to know which wb it came from
Next
End Sub

/Marcus


"Marguerite" wrote:

This doesn't eliminate the problem of needing to access/type in all those
different workbook files... :(

"Eric" wrote:

Marguerite,

You could use INDIRECT, but you'd still need to type in the file names that
you wanted.

Eric


"Marguerite" wrote:

I have a large number of workbooks with identical structures. I want to get
information from a location on a particular sheet in every one of these
workbooks, and list it in a new workbook. Any ideas of how to do this
without having to copy-and-paste from each of the locations or manually enter
the source for each of these books?

Thanks!

Marcus,
Where do you type all of that information? Do you have to put in the name
of the actual sheets where you listed Name.wb, etc?
Debbie

"Marcus Langell" wrote:

Open all workbooks and use this to reference cell Sheet1!$C$1 in all
workbooks in column A of the active sheet:

Sub RefWBs()
c = 0
For Each wb In Application.Workbooks
c = c + 1
ref = "=[" & wb.Name & "]Sheet1!$C$1"
Range("A" & c) = ref
Range("B" & c) = wb.Name 'to know which wb it came from
Next
End Sub

/Marcus


"Marguerite" wrote:

This doesn't eliminate the problem of needing to access/type in all those
different workbook files... :(

"Eric" wrote:

Marguerite,

You could use INDIRECT, but you'd still need to type in the file names that
you wanted.

Eric


"Marguerite" wrote:

I have a large number of workbooks with identical structures. I want to get
information from a location on a particular sheet in every one of these
workbooks, and list it in a new workbook. Any ideas of how to do this
without having to copy-and-paste from each of the locations or manually enter
the source for each of these books?

Thanks!

Marcus,
Where do you type all of this information? Do I need to type the acutal
name of the workbook where you have listed wb.Name?
Debbie

"Marcus Langell" wrote:

Open all workbooks and use this to reference cell Sheet1!$C$1 in all
workbooks in column A of the active sheet:

Sub RefWBs()
c = 0
For Each wb In Application.Workbooks
c = c + 1
ref = "=[" & wb.Name & "]Sheet1!$C$1"
Range("A" & c) = ref
Range("B" & c) = wb.Name 'to know which wb it came from
Next
End Sub

/Marcus


"Marguerite" wrote:

This doesn't eliminate the problem of needing to access/type in all those
different workbook files... :(

"Eric" wrote:

Marguerite,

You could use INDIRECT, but you'd still need to type in the file names that
you wanted.

Eric


"Marguerite" wrote:

I have a large number of workbooks with identical structures. I want to get
information from a location on a particular sheet in every one of these
workbooks, and list it in a new workbook. Any ideas of how to do this
without having to copy-and-paste from each of the locations or manually enter
the source for each of these books?

Thanks!

You add it as a macro in the Visual Basic editor. (Have a look on Debra's
pages http://www.contextures.com/xlvba01.html#Regular)
Then you run that macro from the workbook where you want the result.
Please make backup of the files before doing it if you're not familiar with
macros.

/Marcus

"Debbie" wrote:

Marcus,
Where do you type all of that information? Do you have to put in the name
of the actual sheets where you listed Name.wb, etc?
Debbie

"Marcus Langell" wrote:

Open all workbooks and use this to reference cell Sheet1!$C$1 in all
workbooks in column A of the active sheet:

Sub RefWBs()
c = 0
For Each wb In Application.Workbooks
c = c + 1
ref = "=[" & wb.Name & "]Sheet1!$C$1"
Range("A" & c) = ref
Range("B" & c) = wb.Name 'to know which wb it came from
Next
End Sub

/Marcus


"Marguerite" wrote:

This doesn't eliminate the problem of needing to access/type in all those
different workbook files... :(

"Eric" wrote:

Marguerite,

You could use INDIRECT, but you'd still need to type in the file names that
you wanted.

Eric


"Marguerite" wrote:

I have a large number of workbooks with identical structures. I want to get
information from a location on a particular sheet in every one of these
workbooks, and list it in a new workbook. Any ideas of how to do this
without having to copy-and-paste from each of the locations or manually enter
the source for each of these books?

Thanks!