VBA msgbox not displaying

A procedure which updates a spreadsheet ends with a msgbox indicating the update is complete
However the msgbox never displays

the msgbox is on a line by itself at the end of the procedure

I have a msgbox at the beginning of the procedure asking the user if they want to continue. This msgbox displays.
The only change made before the msgbox stop working:

Before, I was having a code interruption issue
I changed the application enable cancel key to xlErrorHandler
I catch the error and resume
At the end of the procedure, I change the cancel key value back to xlInterrupt

Any help will be great
CABRLU63Asked:
Who is Participating?
 
FarWestCommented:
try to save existing value before you change and then set it back to Original Value

dim EnableCKey as Integer 
EnableCKey = Application.EnableCancelKey
Application.EnableCancelKey = xlErrorHandler
' do code

Application.EnableCancelKey = EnableCKey 

Open in new window

0
 
NorieVBA ExpertCommented:
Can you post the code?
0
 
CABRLU63Author Commented:
Dim wsS As Worksheet                    ' spreadsheet object to receive reference to Source Spread Sheet
    Dim wsD As Worksheet                    ' spreadsheet object to receive reference to Data Spread Sheet
    Dim rngS As Range                       ' range object to receive reference to A Column, Source Sheet date column
    Dim rngS2 As Range                      ' range object to receive reference to O column, source sheet dates column
    Dim rngD As Range                       ' range object to receive reference to D Column, Data Sheet, Transaction Date Column
    Dim rng As Range                        ' range object used in for each loop
    Dim rng2 As Range                       ' range object used in for each loop
    Dim intFirstRow As Integer              ' first row for copy paste special value in source tab
    Dim intLastRow As Integer               ' last row for copy paste special value in source tab columns A - N (before non-core area)
    Dim intLastRow2 As Integer              ' last row for copy paste special value in source tab non-core area
    Dim intIndex As Integer                 ' index used in for loop
    Dim intIndex2 As Integer                ' index used in for loop
    Dim intFirstUnprotectedRow As Integer   ' hold the first unproted row in the source sheet
    Dim dtMaxDate As Date                   ' holds max date value from last quarter
    Dim strFormula As String                ' used to hold the source formulas
    Dim intOffset As Integer                ' holds the offset in determining the correct column to paste formula
    Dim dtCurrQtrDates(100) As Date                     ' array holding transaction dates for source sheet upadate
    Dim intCurrDateIndex As Integer                     ' index in array holding the dates - value is the last index (integer) in the array

   
'*****************************************************************************************************************************************

'******************************************************************************************************************************************
' Assignments
'*******************************************************************************************************************************************

    On Error GoTo handle
   
    Application.EnableCancelKey = xlErrorHandler
   
    If Not blnIsTotalSourceUpdate Then              ' if we are running a total source update, the msg box already appeared
        If MsgBox("Are you sure you want to update the source tab?", vbYesNo, "Source Update") = vbNo Then
            Exit Sub
        End If
    End If

   
   
    Application.ScreenUpdating = False                                                                  ' set screen updating to false
    Application.Calculation = xlCalculationManual                                                       ' set calculations to manual
    Application.EnableEvents = False                                                                    ' set events to false
   
    Set wsS = ThisWorkbook.Sheets(sSOURCESHEET)                                                         ' assign reference to source worksheet
    Set wsD = ThisWorkbook.Sheets(sDATASHEET)                                                           ' assign reference to data worksheet
    Set rngS = wsS.Range("$A:$A")                                                                       ' assign reference to A column - Date Column
    Set rngD = wsD.Range("$E:$E")                                                                       ' assign reference to E column - transaction dates
   
   
   
    intFirstRow = 4                                                                                     ' first record is row 4 in source sheet
   
    '*************************************************************************************************************************************************
    '*** Main Program ********************************************************************************************************************************
    '*************************************************************************************************************************************************

    'Find first row for new data and copy / past value for existing data
    If blnIsTotalSourceUpdate Then                                                                      ' If total source update, make the values 0
        dtMaxDate = 0
        intFirstUnprotectedRow = 4                                                                      ' while loop finds the last row to assign values of 0
        While (Not wsS.Range("$A" & intFirstUnprotectedRow).Value = "")
            intFirstUnprotectedRow = intFirstUnprotectedRow + 1
        Wend
        wsS.Range("$B$" & intFirstRow & ":" & "$G$" & intFirstUnprotectedRow - 1).Value = 0             ' set values in source sheet to 0
        wsS.Range("$I$" & intFirstRow & ":" & "$T$" & intFirstUnprotectedRow - 1).Value = 0             ' use two ranges since H is a hidden column
        intLastRow = 3
                                                                                                        ' If not a total source update, find first row new data
    Else                                                                                                ' copy paste values for all rows above the first row for new data
        For Each rng In rngS                                                                            ' interate through column A to find first row needing data
            If rng.Value = 0 And IsDate(rng.Value) Then                                                 ' if cell format is date and the value is 0, the first row is found
                intLastRow = rng.Row - 1                                                                ' assign last row which is the last row having a date value - not 0
                If intLastRow = 3 Then                                                                  ' If intLastRow is 3, the source sheet is being used for first time
                    dtMaxDate = 0                                                                       ' If source is being used for first time, set max date to 0
                Else                                                                                    ' If source sheet been used, get max date and copy past values for existing data
                    dtMaxDate = rng.Offset(-1, 0).Value                                                 ' max date is one row above the first row needing new data
                    wsS.Range("$B$" & intFirstRow & ":" & "$G$" & intLastRow).Copy                      ' copy / paste values existing data
                    wsS.Range("$B$" & intFirstRow & ":" & "$G$" & intLastRow).PasteSpecial xlPasteValues
                    wsS.Range("$I$" & intFirstRow & ":" & "$N$" & intLastRow).Copy
                    wsS.Range("$I$" & intFirstRow & ":" & "$N$" & intLastRow).PasteSpecial xlPasteValues
                     SendKeys ("{ESC}")
                End If
                Exit For
            End If
        Next
   
    End If
   
    'get all transaction dates in data sheet greater than max date and assign to array
    intCurrDateIndex = 0                                                                           ' set array index to 0
    For Each rng2 In rngD                                                                          ' interate through transaction date column in data sheet
        If IsDate(rng2.Value) And rng2.Value > dtMaxDate Then                                      ' If trans date > max date, enter into array
            If intCurrDateIndex = 0 Then                                                           ' If first array entry, put value in array and increment index
                dtCurrQtrDates(intCurrDateIndex) = rng2.Value
                intCurrDateIndex = intCurrDateIndex + 1
            Else                                                                                   ' If entries in array, check for duplicate value
                For intIndex = 0 To intCurrDateIndex - 1                                           ' iterate through array
                    If rng2.Value = dtCurrQtrDates(intIndex) Then                                  ' If date value found in array, exit for and go to next trans date
                        Exit For
                    ElseIf intIndex = intCurrDateIndex - 1 Then                                    ' If the trans date is not in array, assign value to array
                        dtCurrQtrDates(intCurrDateIndex) = rng2.Value
                        sortDates dtCurrQtrDates, intCurrDateIndex                                 ' sort array
                        intCurrDateIndex = intCurrDateIndex + 1                                    ' increment array index
                    End If
                Next
            End If
        End If
       
        If rng2.Value = "" And rng2.Offset(1, 0).Value = "" And rng2.Offset(2, 0).Value = "" And rng2.Row > 12 Then 'if we are at end of data sheet, exit for
            Exit For
        End If
       
    Next
   
    'enter trans dates and formulas in new data rows in source sheet
    For intIndex = 0 To intCurrDateIndex - 1                                                                        'iterate through trans date array
        wsS.Range("A" & intLastRow + 1).Offset(intIndex, 0).Value = dtCurrQtrDates(intIndex)                        ' enter trans date in column A
        For intIndex2 = 1 To Range("formulas").Rows.Count                                                           ' enter the 8 formulas
            If Range("formulas").Cells(intIndex2, 3) = "A" Then
                intOffset = Range("Offset" & intIndex2).Value                                                           ' get offset from date column in source sheet
                Range("Formula" & intIndex2).Copy                                                                       ' copy formula in setup sheet and paste formula in source sheet
                wsS.Range("A" & intLastRow + 1).Offset(intIndex, 0).Offset(0, intOffset).PasteSpecial xlPasteFormulas   ' paste formula
            End If
        Next
    Next
   
   
    Set wsS = Nothing
    Set wsD = Nothing
    Set rngS = Nothing
    Set rngD = Nothing
    Set rng = Nothing
    Set rng2 = Nothing
   
    sourceUpdate2
   
    MsgBox "source update is complete", vbOKOnly, "Source Update"
   
   
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
   

   
    Application.EnableCancelKey = xlInterrupt



   
   
 Exit Sub
handle:
 If Err.Number = 18 Then
    Resume Next
 Else
    MsgBox "error message: " & Err.Description & "  Click ok and code will continue", vbOKOnly
    Resume Next
 End If
 

End Sub
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jeff DarlingDeveloper AnalystCommented:
Here are some suggestions to get you started.

1. Use OPTION EXPLICIT


When you do code, it is important that all the variables used get declared. If you fail to declare everything, you will get unpredictable results.  code OPTION EXPLICIT at the top of every module.

Please use the Code block feature in EE :)

Option Explicit

Public Sub foo()


Dim wsS As Worksheet                    ' spreadsheet object to receive reference to Source Spread Sheet
    Dim wsD As Worksheet                    ' spreadsheet object to receive reference to Data Spread Sheet
    Dim rngS As Range                       ' range object to receive reference to A Column, Source Sheet date column
    Dim rngS2 As Range                      ' range object to receive reference to O column, source sheet dates column
    Dim rngD As Range                       ' range object to receive reference to D Column, Data Sheet, Transaction Date Column
    Dim rng As Range                        ' range object used in for each loop
    Dim rng2 As Range                       ' range object used in for each loop
    Dim intFirstRow As Integer              ' first row for copy paste special value in source tab
    Dim intLastRow As Integer               ' last row for copy paste special value in source tab columns A - N (before non-core area)
    Dim intLastRow2 As Integer              ' last row for copy paste special value in source tab non-core area
    Dim intIndex As Integer                 ' index used in for loop
    Dim intIndex2 As Integer                ' index used in for loop
    Dim intFirstUnprotectedRow As Integer   ' hold the first unproted row in the source sheet
    Dim dtMaxDate As Date                   ' holds max date value from last quarter
    Dim strFormula As String                ' used to hold the source formulas
    Dim intOffset As Integer                ' holds the offset in determining the correct column to paste formula
    Dim dtCurrQtrDates(100) As Date                     ' array holding transaction dates for source sheet upadate
    Dim intCurrDateIndex As Integer                     ' index in array holding the dates - value is the last index (integer) in the array
    Dim blnIsTotalSourceUpdate As Boolean
    

    
'*****************************************************************************************************************************************

'******************************************************************************************************************************************
' Assignments
'*******************************************************************************************************************************************

    On Error GoTo handle
    
    Application.EnableCancelKey = xlErrorHandler
    
    If Not blnIsTotalSourceUpdate Then              ' if we are running a total source update, the msg box already appeared
        If MsgBox("Are you sure you want to update the source tab?", vbYesNo, "Source Update") = vbNo Then
            Exit Sub
        End If
    End If

    
    
    Application.ScreenUpdating = False                                                                  ' set screen updating to false
    Application.Calculation = xlCalculationManual                                                       ' set calculations to manual
    Application.EnableEvents = False                                                                    ' set events to false
    
    Set wsS = ThisWorkbook.Sheets("sSOURCESHEET")                                                         ' assign reference to source worksheet
    Set wsD = ThisWorkbook.Sheets("sDATASHEET")                                                           ' assign reference to data worksheet
    Set rngS = wsS.Range("$A:$A")                                                                       ' assign reference to A column - Date Column
    Set rngD = wsD.Range("$E:$E")                                                                       ' assign reference to E column - transaction dates
    
    
    
    intFirstRow = 4                                                                                     ' first record is row 4 in source sheet
    
    '*************************************************************************************************************************************************
    '*** Main Program ********************************************************************************************************************************
    '*************************************************************************************************************************************************

    'Find first row for new data and copy / past value for existing data
    If blnIsTotalSourceUpdate Then                                                                      ' If total source update, make the values 0
        dtMaxDate = 0
        intFirstUnprotectedRow = 4                                                                      ' while loop finds the last row to assign values of 0
        While (Not wsS.Range("$A" & intFirstUnprotectedRow).Value = "")
            intFirstUnprotectedRow = intFirstUnprotectedRow + 1
        Wend
        wsS.Range("$B$" & intFirstRow & ":" & "$G$" & intFirstUnprotectedRow - 1).Value = 0             ' set values in source sheet to 0
        wsS.Range("$I$" & intFirstRow & ":" & "$T$" & intFirstUnprotectedRow - 1).Value = 0             ' use two ranges since H is a hidden column
        intLastRow = 3
                                                                                                        ' If not a total source update, find first row new data
    Else                                                                                                ' copy paste values for all rows above the first row for new data
        For Each rng In rngS                                                                            ' interate through column A to find first row needing data
            If rng.Value = 0 And IsDate(rng.Value) Then                                                 ' if cell format is date and the value is 0, the first row is found
                intLastRow = rng.Row - 1                                                                ' assign last row which is the last row having a date value - not 0
                If intLastRow = 3 Then                                                                  ' If intLastRow is 3, the source sheet is being used for first time
                    dtMaxDate = 0                                                                       ' If source is being used for first time, set max date to 0
                Else                                                                                    ' If source sheet been used, get max date and copy past values for existing data
                    dtMaxDate = rng.Offset(-1, 0).Value                                                 ' max date is one row above the first row needing new data
                    wsS.Range("$B$" & intFirstRow & ":" & "$G$" & intLastRow).Copy                      ' copy / paste values existing data
                    wsS.Range("$B$" & intFirstRow & ":" & "$G$" & intLastRow).PasteSpecial xlPasteValues
                    wsS.Range("$I$" & intFirstRow & ":" & "$N$" & intLastRow).Copy
                    wsS.Range("$I$" & intFirstRow & ":" & "$N$" & intLastRow).PasteSpecial xlPasteValues
                     SendKeys ("{ESC}")
                End If
                Exit For
            End If
        Next
    
    End If
    
    'get all transaction dates in data sheet greater than max date and assign to array
    intCurrDateIndex = 0                                                                           ' set array index to 0
    For Each rng2 In rngD                                                                          ' interate through transaction date column in data sheet
        If IsDate(rng2.Value) And rng2.Value > dtMaxDate Then                                      ' If trans date > max date, enter into array
            If intCurrDateIndex = 0 Then                                                           ' If first array entry, put value in array and increment index
                dtCurrQtrDates(intCurrDateIndex) = rng2.Value
                intCurrDateIndex = intCurrDateIndex + 1
            Else                                                                                   ' If entries in array, check for duplicate value
                For intIndex = 0 To intCurrDateIndex - 1                                           ' iterate through array
                    If rng2.Value = dtCurrQtrDates(intIndex) Then                                  ' If date value found in array, exit for and go to next trans date
                        Exit For
                    ElseIf intIndex = intCurrDateIndex - 1 Then                                    ' If the trans date is not in array, assign value to array
                        dtCurrQtrDates(intCurrDateIndex) = rng2.Value
                        sortDates dtCurrQtrDates, intCurrDateIndex                                 ' sort array
                        intCurrDateIndex = intCurrDateIndex + 1                                    ' increment array index
                    End If
                Next
            End If
        End If
        
        If rng2.Value = "" And rng2.Offset(1, 0).Value = "" And rng2.Offset(2, 0).Value = "" And rng2.Row > 12 Then 'if we are at end of data sheet, exit for
            Exit For
        End If
        
    Next
    
    'enter trans dates and formulas in new data rows in source sheet
    For intIndex = 0 To intCurrDateIndex - 1                                                                        'iterate through trans date array
        wsS.Range("A" & intLastRow + 1).Offset(intIndex, 0).Value = dtCurrQtrDates(intIndex)                        ' enter trans date in column A
        For intIndex2 = 1 To Range("formulas").Rows.Count                                                           ' enter the 8 formulas
            If Range("formulas").Cells(intIndex2, 3) = "A" Then
                intOffset = Range("Offset" & intIndex2).Value                                                           ' get offset from date column in source sheet
                Range("Formula" & intIndex2).Copy                                                                       ' copy formula in setup sheet and paste formula in source sheet
                wsS.Range("A" & intLastRow + 1).Offset(intIndex, 0).Offset(0, intOffset).PasteSpecial xlPasteFormulas   ' paste formula
            End If
        Next
    Next
    
    
    Set wsS = Nothing
    Set wsD = Nothing
    Set rngS = Nothing
    Set rngD = Nothing
    Set rng = Nothing
    Set rng2 = Nothing
    
    sourceUpdate2
    
    MsgBox "source update is complete", vbOKOnly, "Source Update"
    
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    

    
    Application.EnableCancelKey = xlInterrupt



    
    
 Exit Sub
handle:
 If Err.Number = 18 Then
    Resume Next
 Else
    MsgBox "error message: " & Err.Description & "  Click ok and code will continue", vbOKOnly
    Resume Next
 End If
 

End Sub

Open in new window

0
 
FarWestCommented:
did you try putting
 Application.EnableCancelKey = xlInterrupt
before showing the message?
also please submit the code again using Code tag in comment editor
thanks
0
 
NorieVBA ExpertCommented:
What happens if you remove On Error Goto handle and run the code?
0
 
Jeff DarlingDeveloper AnalystCommented:
Can you include the code for these 2 functions or subs?

sourceUpdate2
sortDates

I was able to run the code if I comment out these two lines.
0
 
CABRLU63Author Commented:
1. Thanks for advise. I have option explicit in the general declarations
2. Yes, I tried changing the cancel key value back to interrupt before printing message
3. If I remove the error handler, I receive a code interrupt message. This is where my problem began. I researched on the internet and got the idea to change the cancel key value to error handling and catch the error. After making the changes, the msgbox would not display. The code runs, and I get the expected results. However, the user does not get a message that the process is complete.


Public Sub sourceUpdate2()

'*************************************************************************************************************************************************************
'****************************************************Updates Source Tab with new data ************************************************************************
'*** 1. Copy Paste Special Values any formulas from last quarter in Source Tab *******************************************************************************
'*** 2. Obtain all transaction dates greater than the max date without duplicating ***************************************************************************
'*** 3. Assign date to next available row, after last row previous qtr, in column A labeled Date *************************************************************
'*** 4. Copy formulas from setup tab under label Source Formulas and paste special formula in source date in row with new date *******************************
'***    a. The offset number next to the formula detrmines which column receives the formula. The offset is from column A ************************************
'***    b. Each formula in the setup is pasted in the proper column based on the offset in the new date row (code loops 8 times) *****************************
'*** 5. Steps 3 to 5 is repeated for each transaction date
'**************************************************************************************************************************************************************

'***********************************************************************************************************************
' Declarations
'***********************************************************************************************************************
    Dim wsS As Worksheet                    ' spreadsheet object to receive reference to Source Spread Sheet
    Dim wsD As Worksheet                    ' spreadsheet object to receive reference to Data Spread Sheet
    Dim rngS As Range                       ' range object to receive reference to A Column, Source Sheet date column
    Dim rngD As Range                       ' range object to receive reference to D Column, Data Sheet, Transaction Date Column
    Dim rng As Range                        ' range object used in for each loop
    Dim rng2 As Range                       ' range object used in for each loop
    Dim intFirstRow As Integer              ' first row for copy paste special value in source tab
    Dim intLastRow As Integer               ' last row for copy paste special value in source tab columns A - N (before non-core area)
    Dim intIndex As Integer                 ' index used in for loop
    Dim intIndex2 As Integer                ' index used in for loop
    Dim intFirstUnprotectedRow As Integer   ' hold the first unproted row in the source sheet
    Dim dtMaxDate As Date                   ' holds max date value from last quarter
    Dim strFormula As String                ' used to hold the source formulas
    Dim intOffset As Integer                ' holds the offset in determining the correct column to paste formula
    Dim dtCurrQtrDates(100) As Date                     ' array holding transaction dates for source sheet upadate
    Dim intCurrDateIndex As Integer                     ' index in array holding the dates - value is the last index (integer) in the array

    
'*****************************************************************************************************************************************

'******************************************************************************************************************************************
' Assignments
'*******************************************************************************************************************************************
    
    On Error GoTo handle
    
    Set wsS = ThisWorkbook.Sheets(sSOURCESHEET)                                                         ' assign reference to source worksheet
    Set wsD = ThisWorkbook.Sheets(sDATASHEET)                                                           ' assign reference to data worksheet
    Set rngS = wsS.Range("$O:$O")                                                                       ' assign reference to A column - Date Column
    Set rngD = wsD.Range("$E:$E")                                                                       ' assign reference to E column - transaction dates
    
    
    
    intFirstRow = 4                                                                                     ' first record is row 4 in source sheet
    
    '*************************************************************************************************************************************************
    '*** Main Program ********************************************************************************************************************************
    '*************************************************************************************************************************************************

    'Find first row for new data and copy / past value for existing data
    If blnIsTotalSourceUpdate Then                                                                      ' If total source update, make the values 0
        dtMaxDate = 0
        intFirstUnprotectedRow = 4                                                                      ' while loop finds the last row to assign values of 0
        intLastRow = 3
    Else                                                                                                ' copy paste values for all rows above the first row for new data
        For Each rng In rngS                                                                            ' interate through column A to find first row needing data
            If rng.Value = 0 And IsDate(rng.Value) Then                                                 ' if cell format is date and the value is 0, the first row is found
                intLastRow = rng.Row - 1                                                                ' assign last row which is the last row having a date value - not 0
                If intLastRow = 3 Then                                                                  ' If intLastRow is 3, the source sheet is being used for first time
                    dtMaxDate = 0                                                                       ' If source is being used for first time, set max date to 0
                Else                                                                                    ' If source sheet been used, get max date and copy past values for existing data
                    dtMaxDate = rng.Offset(-1, 0).Value                                                 ' max date is one row above the first row needing new data
                    wsS.Range("$P$" & intFirstRow & ":" & "$T$" & intLastRow).Copy                      ' copy / paste values existing data
                    wsS.Range("$P$" & intFirstRow & ":" & "$T$" & intLastRow).PasteSpecial xlPasteValues
                    SendKeys ("{ESC}")
                End If
                Exit For
            End If
        Next
    
    End If
    
    intCurrDateIndex = 0                                                                           ' set array index to 0
    For Each rng2 In rngD                                                                          ' interate through transaction date column in data sheet
        If IsDate(rng2.Value) And rng2.Value > dtMaxDate Then                                      ' If trans date > max date, enter into array
            If intCurrDateIndex = 0 Then                                                           ' If first array entry, put value in array and increment index
                dtCurrQtrDates(intCurrDateIndex) = rng2.Value
                intCurrDateIndex = intCurrDateIndex + 1
            Else                                                                                   ' If entries in array, check for duplicate value
                For intIndex = 0 To intCurrDateIndex - 1                                           ' iterate through array
                    If rng2.Value = dtCurrQtrDates(intIndex) Then                                  ' If date value found in array, exit for and go to next trans date
                        Exit For
                    ElseIf intIndex = intCurrDateIndex - 1 Then                                    ' If the trans date is not in array, assign value to array
                        dtCurrQtrDates(intCurrDateIndex) = rng2.Value
                        sortDates dtCurrQtrDates, intCurrDateIndex                                 ' sort array
                        intCurrDateIndex = intCurrDateIndex + 1                                    ' increment array index
                    End If
                Next
            End If
        End If
        
        If rng2.Value = "" And rng2.Offset(1, 0).Value = "" And rng2.Offset(2, 0).Value = "" And rng2.Row > 12 Then 'if we are at end of data sheet, exit for
            Exit For
        End If
        
    Next

    
    
    'enter trans dates and formulas in new data rows in source sheet
    For intIndex = 0 To intCurrDateIndex - 1                                                                        'iterate through trans date array
        wsS.Range("O" & intLastRow + 1).Offset(intIndex, 0).Value = dtCurrQtrDates(intIndex)                        ' enter trans date in column A
        For intIndex2 = 1 To Range("formulas").Rows.Count                                                           ' enter the 8 formulas
            If Range("formulas").Cells(intIndex2, 3) = "O" Then
                intOffset = Range("Offset" & intIndex2).Value                                                           ' get offset from date column in source sheet
                Range("Formula" & intIndex2).Copy                                                                       ' copy formula in setup sheet and paste formula in source sheet
                wsS.Range("O" & intLastRow + 1).Offset(intIndex, 0).Offset(0, intOffset).PasteSpecial xlPasteFormulas   ' paste formula
            End If
        Next
    Next
    

    
    Set wsS = Nothing
    Set wsD = Nothing
    Set rngS = Nothing
    Set rngD = Nothing
    Set rng = Nothing
    Set rng2 = Nothing
    


 Exit Sub
handle:
 If Err.Number = 18 Then
    Resume Next
 Else
    MsgBox "error message: " & Err.Description & "  Click ok and code will continue", vbOKOnly
    Resume Next
 End If
    
    

End Sub

Private Sub sortDates(ByRef sortArray() As Date, ByVal intArraySize)
    Dim intArrayIndex As Integer
    Dim dtTempDate As Date
    
    
    For intArrayIndex = intArraySize To 1 Step -1
        If sortArray(intArrayIndex) < sortArray(intArrayIndex - 1) Then
            dtTempDate = sortArray(intArrayIndex)
            sortArray(intArrayIndex) = sortArray(intArrayIndex - 1)
            sortArray(intArrayIndex - 1) = dtTempDate
        Else
            Exit For
        End If
    Next

End Sub

Open in new window

0
 
Jeff DarlingDeveloper AnalystCommented:
I found three variables not declared.

blnIsTotalSourceUpdate
sSOURCESHEET
sDATASHEET

Where do these variables get set?

sSOURCESHEET
sDATASHEET

    Set wsS = ThisWorkbook.Sheets(sSOURCESHEET)                                                         ' assign reference to source worksheet
    Set wsD = ThisWorkbook.Sheets(sDATASHEET)
0
 
CABRLU63Author Commented:
great thanks!
I't worked, and I'm getting the complete message

thanks you so much!
0
 
FarWestCommented:
you are welcome, and glad I was able to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.