Section of Code in Macro Not "Executing"

[DPRapson]

Hi All,

Can someone please assist by explaining why the section of code marked in red (see below) is not "executing/running"and how can I correct the problem.

I have no real programming experience so I am stuck. I tried googling for a solution but this was unproductive.

I ran the section of code on its own in a new workbook and it worked fine.

The rest of the code in the macro works fine.

Thank you

Dave Rapson

Sub SaveInvoice()
    Application.ScreenUpdating = False
    
    Dim strFilename, strDirname, strPathname, strDefpath As String
    
    With ActiveSheet
        On Error Resume Next ' If directory exist goto next line
        strDirname = Format([Date], "yyyy") ' New directory name


        strFilename = [To] & "-" & Format(Date, "yyyymmdd") & "-" & Format([InvNo], "000") & [Init] 'New file name
        strDefpath = Application.ActiveWorkbook.Path 'Default path name
        If IsEmpty(strDirname) Then Exit Sub
        If IsEmpty(strFilename) Then Exit Sub


        MkDir strDefpath & "\" & strDirname
        strPathname = strDefpath & "\" & strDirname & "\" & strFilename 'create total string
        
        .Copy
        
        ActiveSheet.Shapes("Save").Visible = False
        ActiveSheet.Shapes("SavePDF").Visible = True


        ActiveWorkbook.SaveAs Filename:=strPathname & ".xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
        
        ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strPathname & ".pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    
        ActiveWorkbook.Close
    End With
    
    Range("A7").Value = Range("A7").Value + 1
    
    Range("E1").Value = "=today()"
    
    Range("A17:A50").Select
    Selection.RowHeight = 15
    
    Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
        Set r1 = Range("B1:C5")
        Set r2 = Range("A13:A16")
        Set myMultiAreaRange = Union(r1, r2)
    myMultiAreaRange.Value = vbNullString
    
[COLOR=#ff0000]    Dim tbl As ListObject[/COLOR]
[COLOR=#ff0000]    Set tbl = ActiveSheet.ListObjects("Table1")[/COLOR]
[COLOR=#ff0000]    [/COLOR]
[COLOR=#ff0000]    With tbl.DataBodyRange[/COLOR]
[COLOR=#ff0000]        If .Rows.Count > 4 Then[/COLOR]
[COLOR=#ff0000]            .Offset(1, 0).Resize(.Rows.Count - 4, .Columns.Count).Rows.Delete[/COLOR]
[COLOR=#ff0000]        End If[/COLOR]
[COLOR=#ff0000]    End With[/COLOR]
    
    Range("B1:C1").Select
    
    If ThisWorkbook.Saved = False Then
        ThisWorkbook.Save
    End If
    
    Application.ScreenUpdating = True
End Sub

 

[DPRapson]

This means the table you are refering to, "Table1", has 1 row. Hence it skip that part of your code because it's less than 4. Make sure that you are refering to the right table if there are more than one.

If the name of the table is correct i suggest you recreate your table and see if anything changes. In that case make sure the table name is corresponding to your reference in the code.

Thank you for your reply.

You are right the problem is with the way I am adding rows to the table. I Inserted a new table with the same name. I then noticed that the macro I use to add the row is not adding a "table" row but a normal row (formatting looked the same). I have changed the AddRow macro to use a listobject command. and now it is working fine.

Once again thank you for taking the time to help a noob. I don't think I would have solved without your inputs

Regards,

Dave Rapson

 

[trimmjens]

Nice to hear that you solved your problem. I'm just happy to help.