Hi,
i am trying to copy cell data from source worksheet to a current active sheet (targetsheet) - i create the macro and it is wokring perfectly but if i press "Cancel" or close the "input box", macro delete the current cell values from activesheet (targetsheet)-
where in attachments Book2.xlsx is source file and A.xlsm is target file.
here is my code:
Code
Sub asit()
Dim aw As Workbook
Dim y As Workbook
Set aw = Application.ActiveWorkbook
Application.ScreenUpdating = False
On Error GoTo Canceled
strFile = "c:\Book2.xlsx"
Set y = Workbooks.Open(Filename:=strFile, ReadOnly:=True)
If aw.Worksheets(1).Name = y.Worksheets(1).Name Then
Dim sFind As String
On Error GoTo Canceled
sFind = InputBox("Please enter the Code.")
If Len(Trim(sFind)) = 1000000000000# Then Exit Sub
Dim sourceSheet As Worksheet
Set sourceSheet = y.Worksheets(1)
Dim rngVis As Range
Dim VisCell As Range
With Intersect(sourceSheet.UsedRange, sourceSheet.Columns("B"))
.AutoFilter 1, sFind
On Error Resume Next
Set rngVis = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
.AutoFilter
End With
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim EmpSheet As Worksheet
Set EmpSheet = aw.Worksheets(1)
If rngVis Is Nothing Then
MsgBox "This is not a valid Code.", vbOKCancel + vbCritical, "Enter Code"
Else
For Each VisCell In rngVis.Cells
EmpSheet.Range("H14").Value = VisCell.Worksheet.Cells(VisCell.Row, "F").Text
EmpSheet.Range("H15").Value = VisCell.Worksheet.Cells(VisCell.Row, "E").Text
Next VisCell
End If
End If
y.Close
Canceled:
End Sub