Actually, it works only when destination file is open-error when closed(code included)
Sub Button1_Click()
On Error GoTo a
Dim Ans As Double
Ans = Application.InputBox("Enter the row number between 12 - 54", "Which row contains the data you want to transfer?")
Select Case Ans
Case 12 To 54
MsgBox "Click OK to transfer row " & Ans, vbInformation, "SBC SouthwesternBell"
Application.ScreenUpdating = False
Range("E" & Ans).Activate
Range(ActiveCell, ActiveCell.Offset(, -4)).Copy
'See if Shopper.xls is open, if not, open it
On Error GoTo b:
Windows("Shopper.xls").Activate
'If the file is not open there will be an error and we GoTo b:
GoTo c:
b:
ChDir "C:\"
Workbooks.Open FileName:="C:\Shopper.xls"
'This will open the Shopper.xls file
c:
'Now when we get to c: the file is open either way,
'so we activate it:
Windows("Shopper.xls").Activate
Sheets("Sheet1").Select
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
'Now save and close Shopper.xls because we don't need it anymore
ActiveWorkbook.Save
ActiveWorkbook.Close
'Clear clipboard
Application.CutCopyMode = False
Application.ScreenUpdating = True
Case 0
MsgBox "You cancelled or " & Ans & " is not in the Valid range", vbExclamation, "Invalid entry"
Exit Sub
Case Else
MsgBox Ans & " is not in the Valid range", vbExclamation, "Invalid entry"
Exit Sub
a:
Exit Sub
End Select
End Sub
Only works when file is open--> Can Anyone Help? no luck here...
Disregard -- Re-Wrote myself