I want to use a macro so that evertime i run an analysis tool in a workbook,
I can copy the data into another file. So my questions are

1) Can I accomplish this without opening the other (index) file. The only
method I know right now is to do something like Windows("Index.xls).Activate
,etc. Is there some way I can automactially open the file?

2)The data added would be appended at the last row of the index file. How
should I accomlish this? I think I would need a FOR loop and a cell<""
check, but how would specify that the macro only looks down one column?
i.e. I would want it to look down column A and skip row 1-10 and then check
against each cell value to see if there's any value that exists in the cell.

3) How do I copy absolute values. ActiveCell = "=[Filename.xls]Main!R3C3"
would give me a link, but I just need the absolute values.

Thanks in advance

Maybe this ?
http://www.rondebruin.nl/copy1.htm

See the last example that use another workbook

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ren" wrote in message ...
I want to use a macro so that evertime i run an analysis tool in a workbook,
I can copy the data into another file. So my questions are

1) Can I accomplish this without opening the other (index) file. The only
method I know right now is to do something like Windows("Index.xls).Activate
,etc. Is there some way I can automactially open the file?

2)The data added would be appended at the last row of the index file. How
should I accomlish this? I think I would need a FOR loop and a cell<""
check, but how would specify that the macro only looks down one column?
i.e. I would want it to look down column A and skip row 1-10 and then check
against each cell value to see if there's any value that exists in the cell.

3) How do I copy absolute values. ActiveCell = "=[Filename.xls]Main!R3C3"
would give me a link, but I just need the absolute values.

Thanks in advance

Thanks,

I do have a few problems though.
VBA doesn't seemt o recognize bIsBookOpen()

How do I copy single cell values

destWB.Worksheets("Index").Cell("A" & Lr).Value =
sourceWB.Worksheets("Main").Cell("C3")

gives me an error message.

As does

Set destrange = destWB.Worksheets("Index").Range("D" & Lr)



"Ron de Bruin" wrote:

Maybe this ?
http://www.rondebruin.nl/copy1.htm

See the last example that use another workbook

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ren" wrote in message ...
I want to use a macro so that evertime i run an analysis tool in a workbook,
I can copy the data into another file. So my questions are

1) Can I accomplish this without opening the other (index) file. The only
method I know right now is to do something like Windows("Index.xls).Activate
,etc. Is there some way I can automactially open the file?

2)The data added would be appended at the last row of the index file. How
should I accomlish this? I think I would need a FOR loop and a cell<""
check, but how would specify that the macro only looks down one column?
i.e. I would want it to look down column A and skip row 1-10 and then check
against each cell value to see if there's any value that exists in the cell.

3) How do I copy absolute values. ActiveCell = "=[Filename.xls]Main!R3C3"
would give me a link, but I just need the absolute values.

Thanks in advance

Hi Ren

Read this below the macro

**Copy this function together with the LastRow function in the module**
Copy this also in the module

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ren" wrote in message ...
Thanks,

I do have a few problems though.
VBA doesn't seemt o recognize bIsBookOpen()

How do I copy single cell values

destWB.Worksheets("Index").Cell("A" & Lr).Value =
sourceWB.Worksheets("Main").Cell("C3")

gives me an error message.

As does

Set destrange = destWB.Worksheets("Index").Range("D" & Lr)



"Ron de Bruin" wrote:

Maybe this ?
http://www.rondebruin.nl/copy1.htm

See the last example that use another workbook

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ren" wrote in message ...
I want to use a macro so that evertime i run an analysis tool in a workbook,
I can copy the data into another file. So my questions are

1) Can I accomplish this without opening the other (index) file. The only
method I know right now is to do something like Windows("Index.xls).Activate
,etc. Is there some way I can automactially open the file?

2)The data added would be appended at the last row of the index file. How
should I accomlish this? I think I would need a FOR loop and a cell<""
check, but how would specify that the macro only looks down one column?
i.e. I would want it to look down column A and skip row 1-10 and then check
against each cell value to see if there's any value that exists in the cell.

3) How do I copy absolute values. ActiveCell = "=[Filename.xls]Main!R3C3"
would give me a link, but I just need the absolute values.

Thanks in advance