Consulting

Results 1 to 2 of 2

Thread: Solved: Code Amend to OK Msg Box Before Close

  1. #1
    VBAX Regular
    Joined
    Aug 2009
    Posts
    44
    Location

    Solved: Code Amend to OK Msg Box Before Close

    I have the following vba code which mdmackillop kindly provided. The code itself works a treat however I was wondering whether or not it is possible to have the code OK a message box before saving and closing the file that it is importing into?

    At present the code imports data into the desired file before saving and closing the said file. What I would like is for the code to import and save data, OK the message box and close the said file.

    The code is as follows:

     
    Sub PepCopyData()
        Dim EmpId As Range
        Dim WB As Workbook
        Dim ws As Worksheet
        Dim c As Range
        Dim Rng As Range
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        On Error Resume Next
        Set WB = Workbooks("Talent Tool.xls")
        If WB Is Nothing Then
            Set WB = Workbooks.Open("C:\Users\Sameep Dave\Documents\HRG\Talent Tool - TEST v2.xls") '<=== Change to suit
        End If
        Set ws = WB.Sheets("People_Data")
        With ThisWorkbook.Sheets("People_Data")
            Set Rng = Range(.Cells(12, 3), .Cells(Rows.Count, 3).End(xlUp))
        End With
        For Each EmpId In Rng
            Set c = ws.Columns(3).Find(EmpId.Value, lookat:=xlWhole)
            If Not c Is Nothing Then
                EmpId.Offset(, 1).Resize(, 9).Copy
                c.Offset(, 1).PasteSpecial xlPasteValues
                EmpId.Offset(, 11).Resize(, 3).Copy
                c.Offset(, 11).PasteSpecial xlPasteValues
                EmpId.Offset(, 15).Resize(, 5).Copy
                c.Offset(, 15).PasteSpecial xlPasteValues
            End If
        Next
        WB.Save
        WB.Close
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        MsgBox ("Data Transferred")
    End Sub
    Any help would be much appreciated.

    Thanks.

  2. #2
    VBAX Regular
    Joined
    Aug 2009
    Posts
    44
    Location
    Please ignore folks - problem resolved!!! I was having a Monday moment.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •