About Us

John

glad we got there in the end.
--
jb

"maywood" wrote:

It's working. Thanks allot for your help!

"john" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

Wow, I think this works now! Thanks for your help!

But now I have 2 other questions:

1. Now I have some trouble with the references in the ImportWkB. How is it
possible to copy only the content and not the formulas?

2. How is it possible to copy only the worksheets 1 to 6 and not 7, 8, etc.?

"john" wrote:

I omitted

Dim sh As Variant
--
jb

"maywood" wrote:

It says "variable sh not defined"!

"john" wrote:

see if this dose what you want:

Sub Import_Sheets()
Dim fname As Variant
Dim NewWkb As Workbook
Dim ImportWkB As Workbook

Set NewWkb = ThisWorkbook

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

If fname < False Then

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

For Each sh In ImportWkB.Sheets

sh.UsedRange.Copy NewWkb.Sheets(sh.Index).Range("B10")

Next sh

ImportWkB.Close False

End If

Set NewWkb = Nothing
Set ImportWkB = Nothing

End Sub
--
jb

"maywood" wrote:

Hi,

I got a question for VBA in Excel 2003:

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

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Importing from CSV over multiple sheets	Verity	Excel Programming	5	November 16th 05 03:44 AM
IMPORTING SHEETS	Rebecca	New Users to Excel	3	June 29th 05 01:04 PM
Importing data from several sheets, to one chart	Martin Thorgaard	Charts and Charting in Excel	2	January 6th 05 03:07 PM
importing sheets	el_peacock	Excel Programming	0	November 21st 04 04:09 PM
Excel VBA - Importing columns from different sheets	Gaston[_2_]	Excel Programming	0	January 29th 04 12:41 AM

Menu

About Us