Message box if error and don't open other workbook

[ashani]

Hi everyone

i'm using the below coding to copy from one workbook to another and it's working perfectly fine. The only issue is that if there is no value in "T3" or different value to other workbook. Error message shows and it leaves the other workbook open to view to users. What I want is :

1) if there is an error message than show Message Box as "Test" and don't open the other workbook.
2) if all ok and updating the other workbook then do it in the background and don't show the other workbook to user.

I'd really appreciate if someone could guide me on this.

Option Explicit

Option Base 1
Sub CopyToMI()
    Dim Cel As Range
    Dim wsc As Worksheet, wst As Worksheet
    Dim wbc As Workbook, wbt As Workbook
    Dim arrc, arrt
    Dim k&, i&, j&, m&

    Set wbc = ThisWorkbook
    Set wsc = wbc.Worksheets("sum")
    arrc = wsc.[B85:D107]
    k = 1: m = 0
    ReDim arrt(1, UBound(arrc, 1) * (UBound(arrc, 2)))
    For i = 1 To UBound(arrc, 1)
        For j = 1 To UBound(arrc, 2)
            arrt(k, m + j) = arrc(i, j)
        Next j
        m = m + UBound(arrc, 2)
    Next i
    
    Set wbt = Workbooks.Open("C:\users\copied")
    Set wst = wbt.Worksheets("copied")
    i = WorksheetFunction.Match(UCase(wsc.[T3]), wst.[A1:A82], 0)
    wst.Cells(i, 2).Resize(UBound(arrt, 1), UBound(arrt, 2)) = arrt

wbt.Save
wbt.Close

End Sub

 

[dmt32]

Hi,
try this update to your code

Sub CopyToMI()
    Dim Cel As Range
    Dim wsc As Worksheet, wst As Worksheet
    Dim wbc As Workbook, wbt As Workbook
    Dim arrc, arrt
    Dim k&, j&, m&
    Dim i As Variant

    On Error GoTo myerror
    Set wbc = ThisWorkbook
    Set wsc = wbc.Worksheets("sum")
    arrc = wsc.[B85:D107]
    k = 1: m = 0
    ReDim arrt(1, UBound(arrc, 1) * (UBound(arrc, 2)))
    For i = 1 To UBound(arrc, 1)
        For j = 1 To UBound(arrc, 2)
            arrt(k, m + j) = arrc(i, j)
        Next j
        m = m + UBound(arrc, 2)
        
    Next i
    Application.ScreenUpdating = False
    Set wbt = Workbooks.Open("C:\users\copied")
    Set wst = wbt.Worksheets("copied")
    i = Application.Match(UCase(wsc.[T3]), wst.[A1:A82], 0)
'Search Text Not Found
    If IsError(i) Then Err.Raise 744
    wst.Cells(CLng(i), 2).Resize(UBound(arrt, 1), UBound(arrt, 2)) = arrt

    wbt.Close True
    Set wbt = Nothing

myerror:
    If Not wbt Is Nothing Then wbt.Close False
    Application.ScreenUpdating = True
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Dave

 

[ashani]

Thank you so much Dave @dmt32
Works perfectly
The only question is that destination file has a password to open, so If I take the password off then works fine. Any chance we can add password in VBA syntax?

thank you once again

 

[dmt32]

Thank you so much Dave @dmt32
Works perfectly
The only question is that destination file has a password to open, so If I take the password off then works fine. Any chance we can add password in VBA syntax?

thank you once again

Replace the Workbooks.Open line of code with following

Set wbt = Workbooks.Open("C:\users\copied", ReadOnly:=False, Password:="mypassword")

enter your password where shown in BOLD

Dave

 

[ashani]

you are amazing Dave - thank you so much.