Copying Values to another Workbook

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" <[email protected]> wrote in message news:[email protected]...
>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" <[email protected]> wrote in message news:[email protected]...
> >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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> >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" <[email protected]> wrote in message news:[email protected]...
> >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
>
>
>