There are more elegant ways, but this is a quick & dirty method:

Option Explicit
Sub Import_Sheet1()
Dim fname As Variant

fname = Application.GetOpenFilename("XLS-Dateien,*.xls")
If fname < False Then
With ActiveSheet
Workbooks.Open fname
Worksheets(1).UsedRange.Copy .Range("Sheet1!B10")
Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")
Worksheets(3).UsedRange.Copy .Range("Sheet3!B10")


ActiveWorkbook.Close False
End With
End If
End Sub


"maywood" wrote:

Hi,

I got a question for VBA in Excel 2003:

I have different .xls-files in a folder. I need to import some data from the
most up-to-date .xls-file in this folder into a new .xls-file

Example:
In the folder "xy" there is the file "sep_09.xls". I need to copy all the
data from the worksheets 1, 2 & 3 to my file "new.xls" (also to the
worksheets 1, 2 & 3)
By pushing a button in my file "new.xls" I want to get those worksheets from
"sep_09.xls" imported into the different worksheets of "new.xls".

At the moment I am using a code, which only can import one separate worksheet:

Code:

 
 
 Option Explicit 
 Sub Import_Sheet1() 
    Dim fname As Variant 
     
    fname = Application.GetOpenFilename("XLS-Dateien,*.xls") 
    If fname < False Then 
       With ActiveSheet 
          Workbooks.Open fname 
          Worksheets(1).UsedRange.Copy .Range("Sheet1!B10") 
          ActiveWorkbook.Close False 
         End With 
         End If 
 End Sub

For the second worksheet I use a second button with the same code (but:
"worksheets(2)" and ("Sheet2!B10").

How can I manage it to import different worksheets at the same time with one
"central button"?