Compile error: Expected End Sub

[dvart]

Private Sub UpdateData()

'Initialise variables
    strDataAddress = wbNB.Worksheets("Calculations").Range("rDataAddress").Value
    
'Open data file
    Workbooks.Open (strDataAddress)
    Set wbData = ActiveWorkbook
    Set wsData = wbData.ActiveSheet

'Delete unrequired rows
    wsData.Rows("1:5").Delete
    
End Sub

---

Hi

My above code keeps coming up with the error 'Expected End Sub' but i've no idea why. The routine is called from a public routine that is assigned to a button. Can anybody help as it's driving me mental.

Thank you

 

[steve the fish]

Can you paste the other sub that attached to the button.

 

[dvart]

Hi Steve

This is the code linked to the button and another routine the button calls:

Public Sub UpdateNBReport()


Application.ScreenUpdating = False


Call ClearData
Call UpdateData


Application.ScreenUpdating = True


End Sub


Private Sub ClearData()


'Declare variables
    Dim ws As Worksheet
    Dim lngRowNo As Long


'Initialise variables
    Set ws = ActiveWorkbook.Worksheets("NBReport")
    lngRowNo = ws.Range("A4").End(xlDown).Row


'Delete rows
    ws.Rows("4:" & lngRowNo).Delete


'Clear data in first row (excluding formulas)
    ws.Range("A3:AA3").ClearContents


Set ws = Nothing


End Sub

 

[steve the fish]

I cant see anything wrong there. I presume the wbNB is declared elsewhere. Ive only ever seen the end sub error when it literally isn't present.

 

[dvart]

Yes, all my declarations are at the top of the module before the routines.

I'm no expert but I really couldn't see anything wrong with this either. When you consider there is a separate sub-routine that is working fine I really cannot understand what is wrong.

Thank you for looking anyway.

 

[steve the fish]

The only thing here is that they need to be in the same module. Aside from that there appears nothing else wrong.

 

[dvart]

Last week i re-wrote the routines into one procedure and it worked. I tried it again this week and i received the same error message (Expected End Sub). I'm sure there is nothing wrong with the code (as it worked), but if someone could suggest some reason why I might be having these problems, I'd be very appreciative.

New code is shown below:

Public Sub UpdateNBReport()


frmProcessing.Show vbModeless


DoEvents


    Application.ScreenUpdating = False


'Initialise variables
    Set wbNB = ActiveWorkbook
    Set wsNB = wbNB.Worksheets("NBReport")
    lngRowNo = wsNB.Range("A4").End(xlDown).Row
    strDataAddress = wbNB.Worksheets("Calculations").Range("rDataAddress").Value


'Delete data from the NBreport data table
    wsNB.Rows("4:" & lngRowNo).Delete


'Clear data in first row (excluding formulas)
    wsNB.Range("A3:AA3").ClearContents


DoEvents


'Open data file
    Workbooks.Open (strDataAddress)
    Set wbData = ActiveWorkbook
    Set wsData = wbData.ActiveSheet


'Delete unrequired rows
    wsData.Rows("1:5").Delete


'Identify last row no
    lngRowNo = wsData.Range("A1").End(xlDown).Row
    
DoEvents


'Clear any filters in the data table
    wsNB.Range("A3").Select
    ActiveSheet.ShowAllData
    
'Copy the report into the data table
    wsData.Range("A1:AA" & lngRowNo).Copy Destination:=wbNB.Worksheets("NBReport").Range("A3")
    Application.CutCopyMode = False
    wbData.Close savechanges:=False
    
DoEvents
    
'Identify accounts with entry date over 24 months old and delete them
    wsNB.ListObjects("tNBData").Range.AutoFilter field:=29, Criteria1:=False
    
    lngRowNo = wsNB.Range("A3").End(xlDown).Row
    
    wsNB.Range("$A2:$AA$" & lngRowNo).Offset(1, 0).Select
    lngRowCount = Selection.Rows.Count
    lngColumnCount = Selection.Columns.Count
    
    Selection.Resize(lngRowCount - 1, lngColumnCount).EntireRow.Delete
    
    wsNB.Range("A3").Select
    ActiveSheet.ShowAllData
    wsNB.Range("A1").Select
    


    Set wbNB = Nothing
    Set wsNB = Nothing
    Set wbData = Nothing
    Set wsData = Nothing
    
    Application.ScreenUpdating = True
    
    Unload frmProcessing
End Sub

 

[steve the fish]

Check any Workbook_open macro in the workbook you open.

 

[dvart]

There isn't anything in there. There aren't any macros that aren't initiated manually by the user.

 

[steve the fish]

What happens when you press Debug-Compile VBA Project within the VBA window?