Do you really need multiple filenames or are you just looking to pull all
files within a folder as per your original post. If so then try a variant
of this, though if you look through Ron's examples, this may actually have
come from there anyway:-
Sub CopyAllSheetsToOneFile()
Dim i As Integer
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
Set wbDest = ThisWorkbook
' or alternativwly Set wbDest = Workbooks.Add
With Application.FileSearch
.NewSearch
.LookIn = "C:\TEST"
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count
Set wbSource = Workbooks.Open(.FoundFiles(i))
For Each ws In wbSource.Worksheets
ws.Copy After:=wbDest.Worksheets(wbDest.Worksheets.Count)
Next ws
wbSource.Close
Next i
End With
Application.ScreenUpdating = True
End Sub
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------
"need_some_help" wrote in message
...
Cool This really makes sense. As I modify this to look at multiple
workbooks
(not just worksheets within the same workbook) it gets a little tricky.
Any
suggestions on how to set up the array to read in multiple filenames (i.e.
some variation of this line For Each sh In Sheets(Array("Sheet1",
"Sheet3")))
Thanks much
"Ken Wright" wrote:
Try he-
http://www.rondebruin.nl/tips.htm
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------
"need_some_help" wrote in
message
...
I'm writing a macro that opens a static folder and then copies specific
data
from all the files in that folder to manipulate. I can write the code
to
open a file by specifying the exact filename. I need to learn how to
open
a
file (all the files in a particular folder) one by one without knowing:
1)the filenames
2)how many files are in the folder
ahead of time. Please help