VBA - Importing Sheets

M

maywood

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"?
 
S

Sam Wilson

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
 
M

maywood

This still works only for the first Sheet, but not for the second one.
I get an Error 1004 and when I click on 'Debug' it marks the line:

Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")

Sam Wilson said:
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 said:
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"?
Click to expand...
Click to expand...
 
M

maywood

BTW: The error has the commet:
Run-time error '1004': Application-defined or object-defined error

maywood said:
This still works only for the first Sheet, but not for the second one.
I get an Error 1004 and when I click on 'Debug' it marks the line:

Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")

Sam Wilson said:
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 said:
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"?
Click to expand...
Click to expand...
Click to expand...
 
J

john

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
 
S

Sam Wilson

Is there a sheet called "Sheet2" in your target workbook?


maywood said:
This still works only for the first Sheet, but not for the second one.
I get an Error 1004 and when I click on 'Debug' it marks the line:

Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")

Sam Wilson said:
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 said:
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"?
Click to expand...
Click to expand...
Click to expand...
 
M

maywood

Yes.

Sam Wilson said:
Is there a sheet called "Sheet2" in your target workbook?


maywood said:
This still works only for the first Sheet, but not for the second one.
I get an Error 1004 and when I click on 'Debug' it marks the line:

Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")

Sam Wilson said:
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


:

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"?
Click to expand...
Click to expand...
Click to expand...
 
M

maywood

It says "variable sh not defined"!

john said:
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 said:
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"?
Click to expand...
Click to expand...
 
S

Sam Wilson

What is the used range in Worksheets(2)? If it's every row or column and you
try to paste into any cell other than A1 it won't work.

maywood said:
Yes.

Sam Wilson said:
Is there a sheet called "Sheet2" in your target workbook?


maywood said:
This still works only for the first Sheet, but not for the second one.
I get an Error 1004 and when I click on 'Debug' it marks the line:

Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")

:

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


:

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"?
Click to expand...
Click to expand...
Click to expand...
 
J

john

I omitted

Dim sh As Variant
--
jb


maywood said:
It says "variable sh not defined"!

john said:
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 said:
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"?
Click to expand...
Click to expand...
Click to expand...
 
M

maywood

The used range is Q1:AS169

The funny thing is, that if use a second button in Sheet 2 with the code:

Private Sub CommandButton1_Click()
Dim fname As Variant

fname = Application.GetOpenFilename("Excel-Data-Input,*.xls")
If fname <> False Then
With ActiveSheet
Workbooks.Open fname
Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")
ActiveWorkbook.Close False
End With
End If
End Sub

it works...?

Sam Wilson said:
What is the used range in Worksheets(2)? If it's every row or column and you
try to paste into any cell other than A1 it won't work.

maywood said:
Yes.

Sam Wilson said:
Is there a sheet called "Sheet2" in your target workbook?


:

This still works only for the first Sheet, but not for the second one.
I get an Error 1004 and when I click on 'Debug' it marks the line:

Worksheets(2).UsedRange.Copy .Range("Sheet2!B10")

:

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


:

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"?
Click to expand...
Click to expand...
Click to expand...
 
M

maywood

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 said:
I omitted

Dim sh As Variant
--
jb


maywood said:
It says "variable sh not defined"!

john said:
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


:

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"?
Click to expand...
Click to expand...
Click to expand...
 
J

john

give this a try

Sub Import_Sheets()
Dim fname As Variant
Dim NewWkb As Workbook
Dim ImportWkB As Workbook
Dim sh As Variant
Dim DestRange As Range

Set NewWkb = ThisWorkbook

fname = Application.GetOpenFilename("XLS-Dateien,*.xls")

If fname <> False Then

Application.ScreenUpdating = False

Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True)

For Each sh In ImportWkB.Sheets

Set DestRange = NewWkb.Sheets(sh.Index).Range("B10")

sh.UsedRange.Copy

DestRange.PasteSpecial xlPasteValues, , False, False

If sh.Index > 6 Then Exit For

Next sh

ImportWkB.Close False

With Application

.CutCopyMode = False
.ScreenUpdating = True

End With

End If

Set NewWkb = Nothing
Set ImportWkB = Nothing

End Sub
--
jb


maywood said:
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 said:
I omitted

Dim sh As Variant
--
jb


maywood said:
It says "variable sh not defined"!

:

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


:

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"?
Click to expand...
Click to expand...
Click to expand...
 
M

maywood

Wow, that works. You're great. Thanks for your help.

Just one little problem left: The column header is a date. And the macro
transforms Jan 08 (01.01.2008) into 39448. Why?

john said:
give this a try

Sub Import_Sheets()
Dim fname As Variant
Dim NewWkb As Workbook
Dim ImportWkB As Workbook
Dim sh As Variant
Dim DestRange As Range

Set NewWkb = ThisWorkbook

fname = Application.GetOpenFilename("XLS-Dateien,*.xls")

If fname <> False Then

Application.ScreenUpdating = False

Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True)

For Each sh In ImportWkB.Sheets

Set DestRange = NewWkb.Sheets(sh.Index).Range("B10")

sh.UsedRange.Copy

DestRange.PasteSpecial xlPasteValues, , False, False

If sh.Index > 6 Then Exit For

Next sh

ImportWkB.Close False

With Application

.CutCopyMode = False
.ScreenUpdating = True

End With

End If

Set NewWkb = Nothing
Set ImportWkB = Nothing

End Sub
--
jb


maywood said:
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 said:
I omitted

Dim sh As Variant
--
jb


:

It says "variable sh not defined"!

:

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


:

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"?
Click to expand...
Click to expand...
Click to expand...
 
J

john

That's because you requested values only to be copied.
Microsoft Excel stores dates as sequential serial numbers so they can be
used in calculations. By default, January 1, 1900 is serial number 1, and
January 1, 2008 is serial number 39448 because it is 39,448 days after
January 1, 1900. By not copying the format of your date, you get the result
you now have.

See if this modification works for you.

Sub Import_Sheets()
Dim fname As Variant
Dim NewWkb As Workbook
Dim ImportWkB As Workbook
Dim sh As Variant
Dim DestRange As Range

Set NewWkb = ThisWorkbook

fname = Application.GetOpenFilename("XLS-Dateien,*.xls")

If fname <> False Then

Application.ScreenUpdating = False

Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True)

For Each sh In ImportWkB.Sheets

Set DestRange = NewWkb.Sheets(sh.Index).Range("B10")

sh.UsedRange.Copy

With DestRange

.PasteSpecial xlPasteValues, , False, False
.PasteSpecial xlPasteFormats, , False, False

End With


Set DestRange = Nothing

Application.CutCopyMode = False

If sh.Index > 6 Then Exit For

Next sh

ImportWkB.Close False

Application.ScreenUpdating = True


End If

Set NewWkb = Nothing
Set ImportWkB = Nothing

End Sub





--
jb


maywood said:
Wow, that works. You're great. Thanks for your help.

Just one little problem left: The column header is a date. And the macro
transforms Jan 08 (01.01.2008) into 39448. Why?

john said:
give this a try

Sub Import_Sheets()
Dim fname As Variant
Dim NewWkb As Workbook
Dim ImportWkB As Workbook
Dim sh As Variant
Dim DestRange As Range

Set NewWkb = ThisWorkbook

fname = Application.GetOpenFilename("XLS-Dateien,*.xls")

If fname <> False Then

Application.ScreenUpdating = False

Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True)

For Each sh In ImportWkB.Sheets

Set DestRange = NewWkb.Sheets(sh.Index).Range("B10")

sh.UsedRange.Copy

DestRange.PasteSpecial xlPasteValues, , False, False

If sh.Index > 6 Then Exit For

Next sh

ImportWkB.Close False

With Application

.CutCopyMode = False
.ScreenUpdating = True

End With

End If

Set NewWkb = Nothing
Set ImportWkB = Nothing

End Sub
--
jb


maywood said:
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.?

:

I omitted

Dim sh As Variant
--
jb


:

It says "variable sh not defined"!

:

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


:

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"?
Click to expand...
Click to expand...
Click to expand...
 
M

maywood

Yes, that works with the dates. Thanks.

But now another problem:
1. I get another error 1004 because the merged cells are not the same size
--> .PasteSpecial xlPasteValues, , False, False (yellow in debug mode)

I am using some merged cells in my ImportWbk-file (for example in column
B1:B41) as a title for the following columns. How to solve that problem?

And I still have another problem with the sheets:
2. In my ImportWbk there are 10 Sheets. I only need to copy Sheets No. 1 to
No. 6.
In my NewWbk I want to press the button in Sheet No. 1 and then Excel should
import the 6 Sheets to Sheet No.2 to No. 7.
At the moment it imports Sheet No.1 to Sheet No.1. And Excel also imports
Sheet No.7 to Sheet No. 7. I think "If sh.Index > 6 Then Exit For" doesn't
work.


john said:
That's because you requested values only to be copied.
Microsoft Excel stores dates as sequential serial numbers so they can be
used in calculations. By default, January 1, 1900 is serial number 1, and
January 1, 2008 is serial number 39448 because it is 39,448 days after
January 1, 1900. By not copying the format of your date, you get the result
you now have.

See if this modification works for you.

Sub Import_Sheets()
Dim fname As Variant
Dim NewWkb As Workbook
Dim ImportWkB As Workbook
Dim sh As Variant
Dim DestRange As Range

Set NewWkb = ThisWorkbook

fname = Application.GetOpenFilename("XLS-Dateien,*.xls")

If fname <> False Then

Application.ScreenUpdating = False

Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True)

For Each sh In ImportWkB.Sheets

Set DestRange = NewWkb.Sheets(sh.Index).Range("B10")

sh.UsedRange.Copy

With DestRange

.PasteSpecial xlPasteValues, , False, False
.PasteSpecial xlPasteFormats, , False, False

End With


Set DestRange = Nothing

Application.CutCopyMode = False

If sh.Index > 6 Then Exit For

Next sh

ImportWkB.Close False

Application.ScreenUpdating = True


End If

Set NewWkb = Nothing
Set ImportWkB = Nothing

End Sub





--
jb


maywood said:
Wow, that works. You're great. Thanks for your help.

Just one little problem left: The column header is a date. And the macro
transforms Jan 08 (01.01.2008) into 39448. Why?

john said:
give this a try

Sub Import_Sheets()
Dim fname As Variant
Dim NewWkb As Workbook
Dim ImportWkB As Workbook
Dim sh As Variant
Dim DestRange As Range

Set NewWkb = ThisWorkbook

fname = Application.GetOpenFilename("XLS-Dateien,*.xls")

If fname <> False Then

Application.ScreenUpdating = False

Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True)

For Each sh In ImportWkB.Sheets

Set DestRange = NewWkb.Sheets(sh.Index).Range("B10")

sh.UsedRange.Copy

DestRange.PasteSpecial xlPasteValues, , False, False

If sh.Index > 6 Then Exit For

Next sh

ImportWkB.Close False

With Application

.CutCopyMode = False
.ScreenUpdating = True

End With

End If

Set NewWkb = Nothing
Set ImportWkB = Nothing

End Sub
--
jb


:

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.?

:

I omitted

Dim sh As Variant
--
jb


:

It says "variable sh not defined"!

:

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


:

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"?
Click to expand...
Click to expand...
Click to expand...
 
J

john

Excel often has problems copying ranges containing merged cells onto sheets
with merged cells even if the merged areas are the same. The best solution is
to not have merged cells. Next best is to unmerge all cells in the range that
is to receive the copy before pasting in the new data. I have added a line in
the code to unmerge cells on target sheet.

I have modified the code to exclude the first sheet in target workbook.

This thread is now quite old, if you continue to have problems contact me
directly on nospamdt @ btinternet.com (close spaces)

Sub Import_Sheets()
Dim fname As Variant
Dim NewWkb As Workbook
Dim ImportWkB As Workbook
Dim sh As Variant
Dim DestRange As Range

Set NewWkb = ThisWorkbook

fname = Application.GetOpenFilename("XLS-Dateien,*.xls")

If fname <> False Then

Application.ScreenUpdating = False

Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True)

For Each sh In ImportWkB.Sheets

NewWkb.Sheets(sh.Index + 1).Cells.UnMerge

Set DestRange = NewWkb.Sheets(sh.Index + 1).Range("B10")

sh.UsedRange.Copy

With DestRange

.PasteSpecial xlPasteValues, , False, False
.PasteSpecial xlPasteFormats, , False, False

End With


Set DestRange = Nothing

Application.CutCopyMode = False

If sh.Index > 6 Then Exit For


Next sh

ImportWkB.Close False

Application.ScreenUpdating = True


End If

Set NewWkb = Nothing
Set ImportWkB = Nothing

End Sub




--
jb


maywood said:
Yes, that works with the dates. Thanks.

But now another problem:
1. I get another error 1004 because the merged cells are not the same size
--> .PasteSpecial xlPasteValues, , False, False (yellow in debug mode)

I am using some merged cells in my ImportWbk-file (for example in column
B1:B41) as a title for the following columns. How to solve that problem?

And I still have another problem with the sheets:
2. In my ImportWbk there are 10 Sheets. I only need to copy Sheets No. 1 to
No. 6.
In my NewWbk I want to press the button in Sheet No. 1 and then Excel should
import the 6 Sheets to Sheet No.2 to No. 7.
At the moment it imports Sheet No.1 to Sheet No.1. And Excel also imports
Sheet No.7 to Sheet No. 7. I think "If sh.Index > 6 Then Exit For" doesn't
work.


john said:
That's because you requested values only to be copied.
Microsoft Excel stores dates as sequential serial numbers so they can be
used in calculations. By default, January 1, 1900 is serial number 1, and
January 1, 2008 is serial number 39448 because it is 39,448 days after
January 1, 1900. By not copying the format of your date, you get the result
you now have.

See if this modification works for you.

Sub Import_Sheets()
Dim fname As Variant
Dim NewWkb As Workbook
Dim ImportWkB As Workbook
Dim sh As Variant
Dim DestRange As Range

Set NewWkb = ThisWorkbook

fname = Application.GetOpenFilename("XLS-Dateien,*.xls")

If fname <> False Then

Application.ScreenUpdating = False

Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True)

For Each sh In ImportWkB.Sheets

Set DestRange = NewWkb.Sheets(sh.Index).Range("B10")

sh.UsedRange.Copy

With DestRange

.PasteSpecial xlPasteValues, , False, False
.PasteSpecial xlPasteFormats, , False, False

End With


Set DestRange = Nothing

Application.CutCopyMode = False

If sh.Index > 6 Then Exit For

Next sh

ImportWkB.Close False

Application.ScreenUpdating = True


End If

Set NewWkb = Nothing
Set ImportWkB = Nothing

End Sub





--
jb


maywood said:
Wow, that works. You're great. Thanks for your help.

Just one little problem left: The column header is a date. And the macro
transforms Jan 08 (01.01.2008) into 39448. Why?

:

give this a try

Sub Import_Sheets()
Dim fname As Variant
Dim NewWkb As Workbook
Dim ImportWkB As Workbook
Dim sh As Variant
Dim DestRange As Range

Set NewWkb = ThisWorkbook

fname = Application.GetOpenFilename("XLS-Dateien,*.xls")

If fname <> False Then

Application.ScreenUpdating = False

Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True)

For Each sh In ImportWkB.Sheets

Set DestRange = NewWkb.Sheets(sh.Index).Range("B10")

sh.UsedRange.Copy

DestRange.PasteSpecial xlPasteValues, , False, False

If sh.Index > 6 Then Exit For

Next sh

ImportWkB.Close False

With Application

.CutCopyMode = False
.ScreenUpdating = True

End With

End If

Set NewWkb = Nothing
Set ImportWkB = Nothing

End Sub
--
jb


:

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.?

:

I omitted

Dim sh As Variant
--
jb


:

It says "variable sh not defined"!

:

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


:

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"?
Click to expand...
Click to expand...
Click to expand...
 
M

maywood

It's working. Thanks allot for your help!

john said:
Excel often has problems copying ranges containing merged cells onto sheets
with merged cells even if the merged areas are the same. The best solution is
to not have merged cells. Next best is to unmerge all cells in the range that
is to receive the copy before pasting in the new data. I have added a line in
the code to unmerge cells on target sheet.

I have modified the code to exclude the first sheet in target workbook.

This thread is now quite old, if you continue to have problems contact me
directly on nospamdt @ btinternet.com (close spaces)

Sub Import_Sheets()
Dim fname As Variant
Dim NewWkb As Workbook
Dim ImportWkB As Workbook
Dim sh As Variant
Dim DestRange As Range

Set NewWkb = ThisWorkbook

fname = Application.GetOpenFilename("XLS-Dateien,*.xls")

If fname <> False Then

Application.ScreenUpdating = False

Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True)

For Each sh In ImportWkB.Sheets

NewWkb.Sheets(sh.Index + 1).Cells.UnMerge

Set DestRange = NewWkb.Sheets(sh.Index + 1).Range("B10")

sh.UsedRange.Copy

With DestRange

.PasteSpecial xlPasteValues, , False, False
.PasteSpecial xlPasteFormats, , False, False

End With


Set DestRange = Nothing

Application.CutCopyMode = False

If sh.Index > 6 Then Exit For


Next sh

ImportWkB.Close False

Application.ScreenUpdating = True


End If

Set NewWkb = Nothing
Set ImportWkB = Nothing

End Sub




--
jb


maywood said:
Yes, that works with the dates. Thanks.

But now another problem:
1. I get another error 1004 because the merged cells are not the same size
--> .PasteSpecial xlPasteValues, , False, False (yellow in debug mode)

I am using some merged cells in my ImportWbk-file (for example in column
B1:B41) as a title for the following columns. How to solve that problem?

And I still have another problem with the sheets:
2. In my ImportWbk there are 10 Sheets. I only need to copy Sheets No. 1 to
No. 6.
In my NewWbk I want to press the button in Sheet No. 1 and then Excel should
import the 6 Sheets to Sheet No.2 to No. 7.
At the moment it imports Sheet No.1 to Sheet No.1. And Excel also imports
Sheet No.7 to Sheet No. 7. I think "If sh.Index > 6 Then Exit For" doesn't
work.


john said:
That's because you requested values only to be copied.
Microsoft Excel stores dates as sequential serial numbers so they can be
used in calculations. By default, January 1, 1900 is serial number 1, and
January 1, 2008 is serial number 39448 because it is 39,448 days after
January 1, 1900. By not copying the format of your date, you get the result
you now have.

See if this modification works for you.

Sub Import_Sheets()
Dim fname As Variant
Dim NewWkb As Workbook
Dim ImportWkB As Workbook
Dim sh As Variant
Dim DestRange As Range

Set NewWkb = ThisWorkbook

fname = Application.GetOpenFilename("XLS-Dateien,*.xls")

If fname <> False Then

Application.ScreenUpdating = False

Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True)

For Each sh In ImportWkB.Sheets

Set DestRange = NewWkb.Sheets(sh.Index).Range("B10")

sh.UsedRange.Copy

With DestRange

.PasteSpecial xlPasteValues, , False, False
.PasteSpecial xlPasteFormats, , False, False

End With


Set DestRange = Nothing

Application.CutCopyMode = False

If sh.Index > 6 Then Exit For

Next sh

ImportWkB.Close False

Application.ScreenUpdating = True


End If

Set NewWkb = Nothing
Set ImportWkB = Nothing

End Sub





--
jb


:

Wow, that works. You're great. Thanks for your help.

Just one little problem left: The column header is a date. And the macro
transforms Jan 08 (01.01.2008) into 39448. Why?

:

give this a try

Sub Import_Sheets()
Dim fname As Variant
Dim NewWkb As Workbook
Dim ImportWkB As Workbook
Dim sh As Variant
Dim DestRange As Range

Set NewWkb = ThisWorkbook

fname = Application.GetOpenFilename("XLS-Dateien,*.xls")

If fname <> False Then

Application.ScreenUpdating = False

Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True)

For Each sh In ImportWkB.Sheets

Set DestRange = NewWkb.Sheets(sh.Index).Range("B10")

sh.UsedRange.Copy

DestRange.PasteSpecial xlPasteValues, , False, False

If sh.Index > 6 Then Exit For

Next sh

ImportWkB.Close False

With Application

.CutCopyMode = False
.ScreenUpdating = True

End With

End If

Set NewWkb = Nothing
Set ImportWkB = Nothing

End Sub
--
jb


:

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.?

:

I omitted

Dim sh As Variant
--
jb


:

It says "variable sh not defined"!

:

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


:

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: 
[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Coding Help for Beginner 0
Import Worksheets and "Overright" 9
To open an excel file. 1
importing file into a new file 2
Copy rows from other Excel-file 0
ERROR: subscript out of range 6
save file using information from a cell 1
save file as different name until cell is blank 4

Top