Hi,
I have a userform in a master file that inputs a list of .xl file names into
a column(K) in a worksheet(Selection Properties), the list could contain any
number of .xl file names( from K2 to column("K").end(xldown)). What I want to
do is to open all these files, copy all information on sheet 1 in these files
and paste all into one worksheet in the current master excel file. I am
having difficulty with the code for opening the possible range of values in
column "K".
Thanks for your time

Check out GetOpenFileName in VBE help.

I think you can use the example to use the cell contents as the filenames.
Than just loop through all the cells.

Or you could just use workbook.open or Open.

--
steveB

Remove "AYN" from email to respond
"Aonghus" wrote in message
...
Hi,
I have a userform in a master file that inputs a list of .xl file names
into
a column(K) in a worksheet(Selection Properties), the list could contain
any
number of .xl file names( from K2 to column("K").end(xldown)). What I want
to
do is to open all these files, copy all information on sheet 1 in these
files
and paste all into one worksheet in the current master excel file. I am
having difficulty with the code for opening the possible range of values
in
column "K".
Thanks for your time

Thant you for your response Steve
My current macro is:

Sub Process_Str()
Dim rw1 As Long, rw2 As Long
' find first open row in each workbook
rw1 = ThisWorkbook.Sheets("Sheet 3").Cells(Rows.Count,
"A").End_(xlUp).Offset(1, 0).Row
rw2 = ActiveWorkbook.ActiveSheet.Cells(Rows.Count,
"A").End(xlUp).Offset(1,_ 0).Row
Dim fName As String
Dim Wkbk As Workbook
Dim curWks As Worksheet
' transfer opened worksheet to master worksheet
ActiveWorkbookname = "Master file"
ActiveSheetname = "Selection Properties"
Set curWks = ActiveSheet
fName = Worksheets("Selection
Propeties").Range("K2").End(xlDown)............run-time error 9 occurs here
Set Wkbk = Workbook.Open(fName)
ActiveWorkbook.ActiveSheet.Range(Cells(2, 25), Cells(rw2,
25)).Copy
Destination = ThisWorkbook.Sheets("Sheet 3").Cells(rw1, 1)
End Sub

I seem to to be missing something but im not to sure what.




"STEVE BELL" wrote:

Check out GetOpenFileName in VBE help.

I think you can use the example to use the cell contents as the filenames.
Than just loop through all the cells.

Or you could just use workbook.open or Open.

--
steveB

Remove "AYN" from email to respond
"Aonghus" wrote in message
...
Hi,
I have a userform in a master file that inputs a list of .xl file names
into
a column(K) in a worksheet(Selection Properties), the list could contain
any
number of .xl file names( from K2 to column("K").end(xldown)). What I want
to
do is to open all these files, copy all information on sheet 1 in these
files
and paste all into one worksheet in the current master excel file. I am
having difficulty with the code for opening the possible range of values
in
column "K".
Thanks for your time

Not too sure, but -

this line does look suspecious - make sure it is a single line
fName = Worksheets("Selection
Propeties").Range("K2").End(xlDown)............run-time error 9 occurs here

you might want to get the range address and than define fName just to double
check.
fName = Worksheets("Selection
Propeties").Range("K2").End(xlDown).Address
fName = Range(fName).text

(double check my construct, you might need to add the worksheet to the
formula.)

you can also use R1C1 notation
fName=Cells(Worksheets("Selection
Propeties").Range("K2").End(xlDown).Row,11)

In general R1C1 looks like
Worksheets("Sheet1").Cells(x,y)

hope this helps...
--
steveB

Remove "AYN" from email to respond
"Aonghus" wrote in message
...
Thant you for your response Steve
My current macro is:

Sub Process_Str()
Dim rw1 As Long, rw2 As Long
' find first open row in each workbook
rw1 = ThisWorkbook.Sheets("Sheet 3").Cells(Rows.Count,
"A").End_(xlUp).Offset(1, 0).Row
rw2 = ActiveWorkbook.ActiveSheet.Cells(Rows.Count,
"A").End(xlUp).Offset(1,_ 0).Row
Dim fName As String
Dim Wkbk As Workbook
Dim curWks As Worksheet
' transfer opened worksheet to master worksheet
ActiveWorkbookname = "Master file"
ActiveSheetname = "Selection Properties"
Set curWks = ActiveSheet
fName = Worksheets("Selection
Propeties").Range("K2").End(xlDown)............run-time error 9 occurs
here
Set Wkbk = Workbook.Open(fName)
ActiveWorkbook.ActiveSheet.Range(Cells(2, 25), Cells(rw2,
25)).Copy
Destination = ThisWorkbook.Sheets("Sheet 3").Cells(rw1, 1)
End Sub

I seem to to be missing something but im not to sure what.




"STEVE BELL" wrote:

Check out GetOpenFileName in VBE help.

I think you can use the example to use the cell contents as the
filenames.
Than just loop through all the cells.

Or you could just use workbook.open or Open.

--
steveB

Remove "AYN" from email to respond
"Aonghus" wrote in message
...
Hi,
I have a userform in a master file that inputs a list of .xl file names
into
a column(K) in a worksheet(Selection Properties), the list could
contain
any
number of .xl file names( from K2 to column("K").end(xldown)). What I
want
to
do is to open all these files, copy all information on sheet 1 in these
files
and paste all into one worksheet in the current master excel file. I am
having difficulty with the code for opening the possible range of
values
in
column "K".
Thanks for your time