VBA - Importing Sheets

[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:

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

 

[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

 

[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")

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:

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

 

[maywood]

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

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:

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

 

[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

 

[Sam Wilson]

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:

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

 

[maywood]

Yes.

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:

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

 

[maywood]

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:

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

 

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

Yes.

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:

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

 

[john]

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:

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

 

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

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.

Yes.

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:

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

 

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

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:

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

 

[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

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:

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

 

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

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:

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

 

[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

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:

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

 

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

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:

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

 

[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

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.

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:

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

 

[maywood]

It's working. Thanks allot for your help!

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

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.

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:

[/QUOTE][/QUOTE][/QUOTE]

 

[john]

glad we got there in the end.
--
jb

It's working. Thanks allot for your help!