VBA Code refinement

[imelquilolo]

Hi, newbie here.

I have a code that allows me to copy values in specific cells of a workbook to another open workbook that reference the filename in cell J58. The code work as intended and copy values to the other workbook. While I was editing the message box and used Step Into(F8), after this line "Workbooks(Outsource).Sheets("DOL").Range("H22:I22").PasteSpecial Paste:=xlPasteValues", all opened excel files will force closed and open again.

Please educate me what happened and how to refine the code to avoid force closed.

I have not yet tried to used another pc/laptop.

Sub Macro1()

' Macro1 Macro

Dim Answer As VbMsgBoxResult
Dim Outsource As String
Outsource = ThisWorkbook.ActiveSheet.Range("J58").Value

Answer = MsgBox("Please make sure to input the correct Filename and extension of DOL_PRO at Cell J58. Are you sure you want to proceed?", vbYesNo)
    
    If Answer = vbYes Then
    
Application.ScreenUpdating = False

    ThisWorkbook.ActiveSheet.Range("K62:L62").Copy
    ' Force close after I Step Into(F8) the line below
    Workbooks(Outsource).Sheets("DOL").Range("H22:I22").PasteSpecial Paste:=xlPasteValues
    ThisWorkbook.ActiveSheet.Range("K63:L63").Copy
    Workbooks(Outsource).Sheets("DOL").Range("N22:O22").PasteSpecial Paste:=xlPasteValues
    ThisWorkbook.ActiveSheet.Range("K64:L64").Copy
    Workbooks(Outsource).Sheets("DOL").Range("T22:U22").PasteSpecial Paste:=xlPasteValues
    ThisWorkbook.ActiveSheet.Range("O62:P62").Copy
    Workbooks(Outsource).Sheets("DOL").Range("X22:Y22").PasteSpecial Paste:=xlPasteValues
    ThisWorkbook.ActiveSheet.Range("O63:P63").Copy
    Workbooks(Outsource).Sheets("DOL").Range("Z22:AA22").PasteSpecial Paste:=xlPasteValues
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    End If
    
End Sub

Happy Holidays!

 

[Micron]

Can't see anything obvious there. Based on your post I'd say Excel is crashing on you, but you didn't say that you are prompted to do any file recoveries. If in other code you turned off warnings (Application.DisplayAlerts = False) then perhaps you're not seeing a prompt that you should be seeing. I'd always use an error handler if I alter application states (e.g. display of alerts, screen updating) because they can be left in that state if there is a run time error. The error handler ensures those states are restored.

You could try typing this in the immediate window and then hit enter, then try the code again:
Application.DisplayAlerts = True

You could also try temporarily setting the option to 'Break on all errors' and see if that changes anything.

 

[DanteAmor]

Hi @imelquilolo and welcome to MrExcel board!

Try this:

Sub Macro1()
  ' Macro1 Macro
  Dim Answer As VbMsgBoxResult
  Dim Outsource As String
  Dim sh As Worksheet
  
  Outsource = ThisWorkbook.ActiveSheet.Range("J58").Value
  Answer = MsgBox("Please make sure to input the correct Filename and extension of DOL_PRO at Cell J58. Are you sure you want to proceed?", vbYesNo)
  Set sh = ThisWorkbook.ActiveSheet
  
  If Answer = vbYes Then
    With Workbooks(Outsource).Sheets("DOL")
      .Range("H22:I22").Value = sh.Range("K62:L62").Value
      .Range("N22:O22").Value = sh.Range("K63:L63").Value
      .Range("T22:U22").Value = sh.Range("K64:L64").Value
      .Range("X22:Y22").Value = sh.Range("O62:P62").Value
      .Range("Z22:AA22").Value = sh.Range("O63:P63").Value
    End With
  End If
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[imelquilolo]

Hi @DanteAmor,

Your code work flawlessly.

Thank you.