Wow, I think this works now! Thanks for your help!
But now I have 2 other questions:
1. Now I have some trouble with the references in the ImportWkB. How is it
possible to copy only the content and not the formulas?
2. How is it possible to copy only the worksheets 1 to 6 and not 7, 8, etc.?
"john" wrote:
I omitted
Dim sh As Variant
--
jb
"maywood" wrote:
It says "variable sh not defined"!
"john" wrote:
see if this dose what you want:
Sub Import_Sheets()
Dim fname As Variant
Dim NewWkb As Workbook
Dim ImportWkB As Workbook
Set NewWkb = ThisWorkbook
fname = Application.GetOpenFilename("XLS-Dateien,*.xls")
If fname < False Then
Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True)
For Each sh In ImportWkB.Sheets
sh.UsedRange.Copy NewWkb.Sheets(sh.Index).Range("B10")
Next sh
ImportWkB.Close False
End If
Set NewWkb = Nothing
Set ImportWkB = Nothing
End Sub
--
jb
"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"?