I am not very familiar with excel macros but am with Visual Basic. I would
like an example of how to copy one cell from a worksheet to another
worksheet in another workbook based on the below info.

Example: (Source)
Filename: C:\Excel\Values.xls
Worksheet: values
Cell: A:1

Example: (Destination)
Filename: C:\Excel\NewValues.xls
Worksheet: NewValues
Cell: A:1

What would the code be to copy the value.

Thanks

Workbooks("NewValues.xls").Sheets("NewValues").Ran ge("A1").Value
Workbooks("Values.xls").Sheets("values").Range("A1 ").Value

Both workbooks need to be open to do this.



--
kkkni
-----------------------------------------------------------------------
kkknie's Profile: http://www.excelforum.com/member.php...nfo&userid=754
View this thread: http://www.excelforum.com/showthread.php?threadid=26730

Are both files open Brian ?

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


"Brian Shannon" <(E-Mail Removed)> wrote in message news:%(E-Mail Removed)...
>I am not very familiar with excel macros but am with Visual Basic. I would
> like an example of how to copy one cell from a worksheet to another
> worksheet in another workbook based on the below info.
>
> Example: (Source)
> Filename: C:\Excel\Values.xls
> Worksheet: values
> Cell: A:1
>
> Example: (Destination)
> Filename: C:\Excel\NewValues.xls
> Worksheet: NewValues
> Cell: A:1
>
> What would the code be to copy the value.
>
> Thanks
>
>

I havent decided if both workbooks will be open yet. kknie's response will
work if they are both open. How would I do the following if they are not?

Workbooks("NewValues.xls").Sheets("NewValues").Ran ge("A1").Value =
Workbooks("Values.xls").Sheets("values").Range("A1 ").Value

Also, Can you substitute variables for range values? Actually I want the
user to be able to input what column of data they need to be used. Can you
concatinate a variable and a number to be inserted into a range?

Thanks
"Ron de Bruin" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Are both files open Brian ?
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Brian Shannon" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> >I am not very familiar with excel macros but am with Visual Basic. I
would
> > like an example of how to copy one cell from a worksheet to another
> > worksheet in another workbook based on the below info.
> >
> > Example: (Source)
> > Filename: C:\Excel\Values.xls
> > Worksheet: values
> > Cell: A:1
> >
> > Example: (Destination)
> > Filename: C:\Excel\NewValues.xls
> > Worksheet: NewValues
> > Cell: A:1
> >
> > What would the code be to copy the value.
> >
> > Thanks
> >
> >
>
>

I'm pretty sure they need to be open (or at least opened and the
closed) to do a copy.

As for variables, theres a bunch of ways to do it:

Dim s as String
s = "A1"
Range(s).Value = 10

or

Dim i as Integer
i = 1
Range("A" & i).Value = 10

or

Cells(1,1).Value = 10
(where the syntax is Cells(RowNum, ColNum)

As you keep searching the boards, you'll find quite a few more...



--
kkkni
-----------------------------------------------------------------------
kkknie's Profile: http://www.excelforum.com/member.php...nfo&userid=754
View this thread: http://www.excelforum.com/showthread.php?threadid=26730

Hi Brian

If both files in C:\ you can use this example
None of the two files have to be open


Sub copy_to_another_workbook()
Dim destWB As Workbook

Application.ScreenUpdating = False
If bIsBookOpen("NewValues.xls") Then
Set destWB = Workbooks("NewValues.xls")
Else
Set destWB = Workbooks.Open("C:\NewValues.xls")
End If

destWB.Sheets("NewValues").Range("A1").Formula = _
"='C:\[Values.xls]values'!$A1"

With destWB.Sheets("NewValues").Range("A1")
.Value = .Value
End With

destWB.Close True
Application.ScreenUpdating = True
End Sub

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


> Also, Can you substitute variables for range values?
Yes

Dim Rnum As Long
Dim Cnum As Integer
Rnum = 5
Cnum = 5
destWB.Sheets("NewValues").Cells(Rnum, Cnum).Formula = _


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


"Brian Shannon" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>I havent decided if both workbooks will be open yet. kknie's response will
> work if they are both open. How would I do the following if they are not?
>
> Workbooks("NewValues.xls").Sheets("NewValues").Ran ge("A1").Value =
> Workbooks("Values.xls").Sheets("values").Range("A1 ").Value
>
> Also, Can you substitute variables for range values? Actually I want the
> user to be able to input what column of data they need to be used. Can you
> concatinate a variable and a number to be inserted into a range?
>
> Thanks
> "Ron de Bruin" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Are both files open Brian ?
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Brian Shannon" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> >I am not very familiar with excel macros but am with Visual Basic. I
> would
>> > like an example of how to copy one cell from a worksheet to another
>> > worksheet in another workbook based on the below info.
>> >
>> > Example: (Source)
>> > Filename: C:\Excel\Values.xls
>> > Worksheet: values
>> > Cell: A:1
>> >
>> > Example: (Destination)
>> > Filename: C:\Excel\NewValues.xls
>> > Worksheet: NewValues
>> > Cell: A:1
>> >
>> > What would the code be to copy the value.
>> >
>> > Thanks
>> >
>> >
>>
>>
>
>