I am having an issue that I *think* is related to timing. I have the following code, where I have inserted a couple of waits (to try and troubleshoot). If I step through it (i.e. set a breakpoint at the beginning of the sub & then step through it with F8), it works fine. Otherwise, if I just try and run through it at full speed, it just seems to end the sub w/o any messages or anything. In the example below, when running at full speed, it never presents the "wait 2 done" message box - it just quits processing. Any thoughts or tips on running this down?
Thank you for any help - I am totally stuck!!
VBA Code:
Dim SourceWB As Workbook ' holds name of Source workbook (Coles) for easier reference
Dim TargetWB As Workbook ' holds name of Target workbook (County Export) for easier reference
Dim ssheet As Worksheet ' sheet1 in the source (Coles) workbook (to make coding shorter)
Dim tsheet As Worksheet 'sheet1 in the target (County) workbook (to make coding shorter)
Dim Sourcelastrow ' holds NUMBER of last row with Data from Source sheet
Dim Targetlastrow ' holds NUMBER of last row with Data from Target sheet
Dim targetfilepath As String
Dim L As Integer ' length of a cell's contents
Sub Add_Coles()
' integrates phone number (and Name) information from a COLES directory export into the County export
' prompt user to pick exported file from Coles directory
Set xfdCOLESexp = Application.FileDialog(msoFileDialogFilePicker)
xfdCOLESexp.Title = "Choose Coles Export file to add the phone numbers from:"
If xfdCOLESexp.Show = -1 Then
xFdCOLESItem = xfdCOLESexp.SelectedItems(1)
' MsgBox ("Dir: " & xFdCOLESItem)
Else
MsgBox ("You cancelled - terminating process - you will need to start over.")
End
End If
targetfilepath = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
Application.Wait Now + TimeValue("00:00:05")
MsgBox ("wait 1 done")
Set SourceWB = Workbooks.Open(xFdCOLESItem)
Application.Wait Now + TimeValue("00:00:03")
MsgBox ("wait 2 done")
Set ssheet = SourceWB.Sheets(1) ' sheet1 in the COLES workbook (to make coding shorter)
Set TargetWB = Workbooks.Open(targetfilepath)
Set tsheet = TargetWB.Sheets(1) ' sheet1 in the County export workbook (to make coding shorter)
Thank you for any help - I am totally stuck!!