copy value from one workbook to another

[Lanfeng79]

I wanted to set the file name as variable so that I can copy value from various workbooks to one workbook.

While I executed the code below, it asks for name of the file three time. But All I want is one at a time to fill three cells.

what's wrong with my code?

Thanks!

Lan
.......................................................................................................

Sub transfer()
'
' transfer Macro
'


'
Dim SourceFileName As String
Dim SF As String
SourceFileName = InputBox("Source File Name: ", "File Name Please", "Source for test.xlsx")
If Len(SourceFileName) = 0 Then Exit Sub
SF = SourceFileName


Windows("Test for transfer number.xlsm").Activate
ActiveCell.FormulaR1C1 = "='[SF]Sheet1'!R3C3"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "='[SF]Sheet1'!R4C3"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "='[SF]Sheet1'!R5C3"
ActiveCell.Offset(0, -2).Range("A1:C1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
Windows(SF).Activate
ActiveWindow.Close
ActiveCell.Offset(8, 6).Range("A1").Select
End Sub

 

[footoo]

[COLOR=#333333]ActiveCell.FormulaR1C1 = "='[" & SF & "]Sheet1'!R3C3"[/COLOR]

Similarly for the other formulas.

 

[Lanfeng79]

Thank you very much! That's helpful.

[COLOR=#333333]ActiveCell.FormulaR1C1 = "='[" & SF & "]Sheet1'!R3C3"[/COLOR]

Similarly for the other formulas.