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

 

[trimmjens]

Have you tried to go through your code step by step by using F8 in VBA?

If you do that you'll see what actually happens.

 

[Norie]

Are you sure it isn't being executed?

What happens if you remove this, which could be hiding errors.

On Error Resume Next

 

[DPRapson]

Have you tried to go through your code step by step by using F8 in VBA?

If you do that you'll see what actually happens.

Hi trimmjens,

Yes I did. It skips (is not highlighted in yellow) the line of code:

.Offset(1, 0).Resize(.Rows.Count - 4, .Columns.Count).Rows.Delete

and nothing happens at the "end if".

I also ran the F8 on the code in its own workbook. Here the line of code Offset (1,0)... is highlighted in yellow, and at the "end if" the rows are removed from the spreadsheet.

 

[DPRapson]

Are you sure it isn't being executed?

What happens if you remove this, which could be hiding errors.

On Error Resume Next

This made no difference. No errors messages when running through code in question.

 

[trimmjens]

I guess that means that the following line is not true and skips that line.

If .Rows.Count > 4 Then

Try to open the Immediate window (Ctrl + G in VBA) and type in this:

?tbl.DataBodyRange.Rows.Count

If the number you recieve is 4 or less you've got the answer.

 

[DPRapson]

I got "1" the first time round. Not sure if this is relevant but when I ran the code in the intermediate window again, I got a runtime error '424': Object required message.

so the statement is not true? Any suggestions?

 

[trimmjens]

I was a little quick in my reply. If this is only for one sertain sheet it's better to specify the sheet name insted of ActiveSheet.

?Sheets("SheetName").ListObjects("Table1").DataBodyRange.Rows.Count

Change the SheetName in that line to the name of your sheet and put it in the Immediate window and see the result.

 

[DPRapson]

I was a little quick in my reply. If this is only for one sertain sheet it's better to specify the sheet name insted of ActiveSheet.

?Sheets("SheetName").ListObjects("Table1").DataBodyRange.Rows.Count

Change the SheetName in that line to the name of your sheet and put it in the Immediate window and see the result.

I got "1" again.

I also tried your suggestion using the code on its own in a new workbook. Here I got "24" the which is equal to the number of rows in the table i created.

I still do not understand why it works on its own but not in the quotation macro.

Thanks for patience and help so far.

 

[trimmjens]

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.