Hi! I was wondering if anyone could help me. I need to import a column
from another excel file. The thing is, the macro needs to ask you the
name of the file and the column (if it could possible with also a
range. i.e. you input the column "D" and you only want from D3:D1200)
in where the data is and copy them to the active cell.

Until now, I have only made the code ask for the file from which the
data is imported, but the range has to be programmed previously, is
there any function that ask the user to give the range of cells to be
copy? And the range copied in a specific column?

What i am trying to do is that in column A I import the description of
a variable number of products, in column B I import the code of the
product and in column D the number of packages (all of them from the
same file). After that I program another macro the calculates some
values with VLOOKUP, INDEX, MATCH with other files. I am stucked up
with this and can't continue, if someone can help please, it will be
very thankful.

My code is this one:

Sub Example5()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim rnum As Long
Dim MPath As String
Dim SaveDriveDir As String
Dim FName As Variant

SaveDriveDir = CurDir
MPath = "C:\Documents and Settings\ba7268\My Documents"
ChDrive MPath
ChDir MPath

FName = Application.GetOpenFilename(filefilter:="Excel Files
(*.xls), *.xls", _
MultiSelect:=True)
If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1
basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet

For N = LBound(FName) To UBound(FName)
Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("H1:H1500")
SourceRcount = sourceRange.Rows.Count


With sourceRange
Set destrange =
basebook.Worksheets(1).Cells(rnum, "A"). _
Resize(.Rows.Count,
..Columns.Count)
End With
destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
Next
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

Thanks!

Daniel


--
reditz11
------------------------------------------------------------------------
reditz11's Profile: http://www.excelforum.com/member.php...o&userid=31682
View this thread: http://www.excelforum.com/showthread...hreadid=513811

Hi,

Try:

Dim srcrng as range

........
Set mybook = Workbooks.Open(FName(N))
Set srcrng = Application.InputBox("Enter source range", Type:=8)
Set sourceRange = mybook.Worksheets(1).Range(srcrng.Address)


HTH

"reditz11" wrote:


Hi! I was wondering if anyone could help me. I need to import a column
from another excel file. The thing is, the macro needs to ask you the
name of the file and the column (if it could possible with also a
range. i.e. you input the column "D" and you only want from D3:D1200)
in where the data is and copy them to the active cell.

Until now, I have only made the code ask for the file from which the
data is imported, but the range has to be programmed previously, is
there any function that ask the user to give the range of cells to be
copy? And the range copied in a specific column?

What i am trying to do is that in column A I import the description of
a variable number of products, in column B I import the code of the
product and in column D the number of packages (all of them from the
same file). After that I program another macro the calculates some
values with VLOOKUP, INDEX, MATCH with other files. I am stucked up
with this and can't continue, if someone can help please, it will be
very thankful.

My code is this one:

Sub Example5()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim rnum As Long
Dim MPath As String
Dim SaveDriveDir As String
Dim FName As Variant

SaveDriveDir = CurDir
MPath = "C:\Documents and Settings\ba7268\My Documents"
ChDrive MPath
ChDir MPath

FName = Application.GetOpenFilename(filefilter:="Excel Files
(*.xls), *.xls", _
MultiSelect:=True)
If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1
basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet

For N = LBound(FName) To UBound(FName)
Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("H1:H1500")
SourceRcount = sourceRange.Rows.Count


With sourceRange
Set destrange =
basebook.Worksheets(1).Cells(rnum, "A"). _
Resize(.Rows.Count,
.Columns.Count)
End With
destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
Next
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

Thanks!

Daniel


--
reditz11
------------------------------------------------------------------------
reditz11's Profile: http://www.excelforum.com/member.php...o&userid=31682
View this thread: http://www.excelforum.com/showthread...hreadid=513811