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