PDA

View Full Version : Solved: Code Amend to OK Msg Box Before Close



SDave
10-05-2009, 03:29 AM
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.

SDave
10-05-2009, 04:00 AM
Please ignore folks - problem resolved!!! I was having a Monday moment.