Prevent the cell values when press Cancel to stop Macro

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

  • Re: Prevent the cell values when press Cancel to stop Macro

    When there is no value from the InputBox, sFind = "". When it runs the AutoFilter Find, although there are no visible cells below the header row, this line of code

    Set rngVis = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)

    sets rngVis to the cell below the first cell in column B, which, because it's merged, = B3. Since B3 is a valid range and is something, the code below this if statement

    If rngVis Is Nothing Then

    is bypassed and this part of the code runs.

                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

    The simple fix is to add a line of code after the InputBox to check if sFind = ""

    sFind = InputBox("Please enter the Code.")
            If sFind = "" Then Exit Sub

    If I've been helpful, let me know. If I haven't, let me know that too. 

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!