Posted to microsoft.public.excel.programming
|
|
VBA - Importing Sheets
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"?
|