HELP! - Can't Copy Cells With VBA
 

I want to copy a cell from a closed workbook. I have a command button that
opens a workbook as a variable. The workbook opens fine, but I get errors
when it tries to copy the cell. It seems that it's not recognizing the
workbook variable as a workbook. I tried a number of different statements
and nothing seems to work. I'm not sure what's wrong. Any thoughts?
Thanks in advance
Todd


Private Sub CommandButton3_Click()

Dim FName As Variant
Dim WB As Workbook
FName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls", Title:="Please select a file")
If FName = False Then
Exit Sub
End If
Set WB = Workbooks.Open(FName)

WB.Worksheets("Sheet1").Range("C4").Copy _
Destination:=Worksheets("Sheet2").Range("E5")

WB.Close savechanges:=False
End Sub

HELP! - Can't Copy Cells With VBA
 

Hi Todd,

Destination:=Worksheets("Sheet2").Range("E5")

Since you do not qualify the worksheet, this refers to a worksheet in the
active workbook. In your case, the activeworkbook is the newly opened WB.
Thus the copy operation is from and too cells in the opened workbook.

Try qualifying the above line:

Destination:=ThisWorkbook.Worksheets("Sheet2").Ran ge("E5")

---
Regards,
Norman



"TK" wrote in message
...
I want to copy a cell from a closed workbook. I have a command button that
opens a workbook as a variable. The workbook opens fine, but I get errors
when it tries to copy the cell. It seems that it's not recognizing the
workbook variable as a workbook. I tried a number of different statements
and nothing seems to work. I'm not sure what's wrong. Any thoughts?
Thanks in advance
Todd


Private Sub CommandButton3_Click()

Dim FName As Variant
Dim WB As Workbook
FName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls", Title:="Please select a file")
If FName = False Then
Exit Sub
End If
Set WB = Workbooks.Open(FName)

WB.Worksheets("Sheet1").Range("C4").Copy _
Destination:=Worksheets("Sheet2").Range("E5")

WB.Close savechanges:=False
End Sub