open copy paste
 

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

open copy paste
 

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

open copy paste
 

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

open copy paste
 

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