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
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