Visual Basic File selection

[Guest]

I've looked at related postings and have been unable to find the answer. Is
there a way to create a macro that would allow me to pull the same type of
data from different folders? In explaination; I am creating new filles in
which the data comes from existing files. What I am doing is opening the
existing file, copying the first sheet and then pasting it into the new file,
then going on to the next file. I'm sure there is an easier way to do this
but I just don't know how to go about solving the problem. Thank you.
Heath

 

[Ron de Bruin]

Hi Heath

Try this example for all files in C:\Data
the new files will be saved in C:\

Be sure that the file with the code is not in C:\Data

Sub TestFile()
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
mybook.Worksheets(1).Copy
ActiveWorkbook.SaveAs "C:\copy of " & mybook.Name & _
Format(Now, "dd-mm-yy h-mm-ss")
ActiveWorkbook.Close False
mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub