| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Importing Sheets
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
"worksheets(2)" and ("Sheet2!B10"). How can I manage it to import different worksheets at the same time with one "central button"? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
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" 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"? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
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" wrote: 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" 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"? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
BTW: The error has the commet:
Run-time error '1004': Application-defined or object-defined error "maywood" wrote: 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" wrote: 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" 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"? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
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"? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
Is there a sheet called "Sheet2" in your target workbook?
"maywood" wrote: 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" wrote: 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" 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"? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
Yes.
"Sam Wilson" wrote: Is there a sheet called "Sheet2" in your target workbook? "maywood" wrote: 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" wrote: 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" 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"? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
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"? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
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" wrote: Yes. "Sam Wilson" wrote: Is there a sheet called "Sheet2" in your target workbook? "maywood" wrote: 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" wrote: 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" 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"? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
I omitted
Dim sh As Variant -- jb "maywood" wrote: It says "variable sh not defined"! "john" wrote: see if this dose what you want: Sub Import_Sheets() Dim fname As Variant Dim NewWkb As Workbook Dim ImportWkB As Workbook Set NewWkb = ThisWorkbook fname = Application.GetOpenFilename("XLS-Dateien,*.xls") If fname < False Then Set ImportWkB = Workbooks.Open(fname, ReadOnly:=True) For Each sh In ImportWkB.Sheets sh.UsedRange.Copy NewWkb.Sheets(sh.Index).Range("B10") Next sh ImportWkB.Close False End If Set NewWkb = Nothing Set ImportWkB = Nothing End Sub -- jb "maywood" wrote: Hi, I got a question for VBA in Excel 2003: I have different .xls-files in a folder. I need to import some data from the most up-to-date .xls-file in this folder into a new .xls-file Example: In the folder "xy" there is the file "sep_09.xls". I need to copy all the data from the worksheets 1, 2 & 3 to my file "new.xls" (also to the worksheets 1, 2 & 3) By pushing a button in my file "new.xls" I want to get those worksheets from "sep_09.xls" imported into the different worksheets of "new.xls". At the moment I am using a code, which only can import one separate worksheet: Code:
Option Explicit
Sub Import_Sheet1()
Dim fname As Variant
fname = Application.GetOpenFilename("XLS-Dateien,*.xls")
If fname < False Then
With ActiveSheet
Workbooks.Open fname
Worksheets(1).UsedRange.Copy .Range("Sheet1!B10")
ActiveWorkbook.Close False
End With
End If
End Sub
For the second worksheet I use a second button with the same code (but: "worksheets(2)" and ("Sheet2!B10"). How can I manage it to import different worksheets at the same time with one "central button"? |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
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" wrote: 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" wrote: Yes. "Sam Wilson" wrote: Is there a sheet called "Sheet2" in your target workbook? "maywood" wrote: 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" wrote: 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" 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"? |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
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"? |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
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"? |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
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"? |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
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". 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"? |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
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". At the moment I am using a code, which only can import one separate worksheet: [code] |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Importing Sheets
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". |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Importing from CSV over multiple sheets | Excel Programming | |||
| IMPORTING SHEETS | New Users to Excel | |||
| Importing data from several sheets, to one chart | Charts and Charting in Excel | |||
| importing sheets | Excel Programming | |||
| Excel VBA - Importing columns from different sheets | Excel Programming | |||
Linear Mode
