Scanning files within a folder

Danger_DJ

New Member
Joined
Mar 26, 2002
Messages
12
I have multiple (200) workbooks, that I need to extract data from. The data is located in the same range of cells in each workbook. Each workbook is uniquely named. How do I write a macro that will scan all of the workbooks for the same range of cells and then copy the data to a new worksheet so that the data is inserted one row after another?
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hello Danger DJ

can you use something line this

Refer to the same cell or range on multiple sheets
A reference that refers to the same cell or range on multiple sheets is called a 3-D reference.

Click the cell where you want to enter the function.
Type = (equal sign), enter the name of the function, and then type an opening parenthesis.
Functions that can be used in a 3-D reference

SUM - adds numbers

Click the tab for the first worksheet to be referenced.
Hold down SHIFT and click the tab for the last worksheet to be referenced.
Select the cell or range of cells to be referenced.
Complete the formula, and press ENTER.


example:

=SUM(Sheet1:Sheet3!A4) This sums across sHeets 1 thru 3 ,,,,just put the formular in a new sheet and copy the formular down


does this help

pll
 

Danger_DJ

New Member
Joined
Mar 26, 2002
Messages
12
Not quite, actually I would like to run a macro that opens each workbook (not sheet) within a folder. I basically need something on the front and back end of this:

Sheets("Analysis").Select
ActiveWindow.ScrollColumn = 1
Range("A6:K6").Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Range("F4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Where the front end opens *.xls within said folder, and back end pastes in one (separate) sheet row after row until it reaches the last workbook within the folder. This would greatly reduce the time it takes to copy and paste the data, ultimately I need the data to construct a run chart.

Thanks, hope I have explained this better.

DJ
 

rikrak

Active Member
Joined
Aug 21, 2002
Messages
255
Hi DJ,

You mean something like this?

<pre>
Sub cp()
'save the name of the active workbook to paste to
wb = ActiveWorkbook.Name

fName = Dir("C:temp*.xls") ' Retrieve the first filename.
Do While fName <> "" ' Start the loop.
Workbooks.Open FileName:=fName
'select sheet
'copy the data

Workbooks(wb).Activate
'select sheet
'paste data

Workbooks(fName).Close
fName = Dir ' Get next filename.
Loop
End Sub
</pre>
 

Watch MrExcel Video

Forum statistics

Threads
1,128,157
Messages
5,629,027
Members
416,359
Latest member
Juena

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top