Marin,

This looks great. A few more questions:

I am new to VBA and Macros.

Is there a way to specify criteria using an array? (i.e. if row 10="one",
then only pull information from rows 1-9 and put that information in the new
workbook.) If this is too difficult it is not absolutely necessary, though
would be extremely helpful.

Where do I paste this code once it is complete?

Is there a way to create a button that when pressed will update this new
workbook?

I really do appreciate your help.

"Martin Fishlock" wrote:

Dave, the following will do as requested.

You may need to tweek the dir function or consider just a loop if you have a
fixed file name structure (ie week01.xls, week02.xls....).

Option Explicit

Sub getdata()
' directory where the data is with the trailing \
Const cszDir As String = "C:\data\"
' prefix for the file name
Const cszFilePrefix As String = "file"
' extension for the file name
Const cszFileExtension As String = ".xls"

Dim wsd As Worksheet
Dim wbc As Workbook
Dim lRowDst As Long
Dim szFileCur As String
Dim szDir As String

Set wsd = ActiveSheet
lRowDst = 2
szFileCur = Dir(cszDir & cszFilePrefix & _
"*" & cszFileExtension)
Do While szFileCur < ""
Set wbc = Workbooks.Open(szFileCur, , True)
'get data'
wsd.Cells(lRowDst, 1) = wbc.Worksheets(1).Range("A1")
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("C1")
wsd.Cells(lRowDst, 3) = wbc.Worksheets(1).Range("D1")
'....
wbc.Close False
szFileCur = Dir
lRowDst = lRowDst + 1
Loop
End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Dave" wrote:

I need to find a solution to the folowing problem:

There is a network folder with an Exel workbook for every week in the year.
Each Friday it is updated with one new sheet for that week. All the data
contained is in the same format. I need to create a single file that when
one presses a button, will go through all of the workbooks in the network
drive and pull specific information and populate it into the one file. Is
this possible? Any help would be appreciated.