Copy from one worksheet to another

  • Thread starter Thread starter Brian Shannon
  • Start date Start date
B

Brian Shannon

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").Range("A1").Value
Workbooks("Values.xls").Sheets("values").Range("A1").Value

Both workbooks need to be open to do this.
 
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").Range("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
 
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...
 
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?
Click to expand...
Yes

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